Office 2010; Windows7; Visual Studio Basic 2008 (VB.Net)
[Code]...
The following strSQL string is not working as expected when run in VB.Net Interop to Access: (NOTE: The line-feeds " _" are used below are for reading only - are not in actual code line)
[Code]...
I am converting old VB6 code to VB.NET with ADO.NET (OleDB). This is my query that will create a blank table when ran in VB.NET, but then works when ran directly in Access. This code presumably also works in VB6, as I am using the same SQL:
SELECT qryAsOf.name, qryAsOf.type, 0 as opt, 0 as swap
INTO qryCon
FROM qryAsOf
LEFT JOIN qryLinked on qryAsOf.c = qryLinked.lc
I feel like this has something to do with the left join and select into being together, but like I said it is only VB that has an issue with it, Access handles it perfectly.Without the INTO line, this query returns all 600+ rows.
This DOES NOT WORK:
cm.CommandText = "CREATE TABLE qryCon (etc...)"
cm.ExecuteNonQuery()
cm.CommandText = "INSERT INTO qryCon SELECT ..." '(rest of query above without INTO line)
cm.ExecuteNonQuery
This DOES WORK:
cm.CommandText = "CREATE PROC qryCon AS SELECT ..." '(same select as above without INTO, again)
cm.ExecuteNonQuery
The CREATE PROC that does work is fine, except I need to insert data into it later, so I get errors about how I need an updatable table. I really want the end qryCon to be a table, but I can't seem to get that to work :(
*However, when I do something like this (using the stored proc (renamed) above which, if viewed in Access, is full of data)
cm.CommandText = "SELECT * FROM storedProc"
dr = cm.ExecuteReader
[code]....
This DOES NOT WORK! By the way, I removed the concatenation in the query for readability. It is correct in the actual project.
I have found the class code below which should be activated to show the result for the selected option
Dim pc as new CheckPerm
pc.Permission = "Modify"
if pc.CheckPerm("C:WIndowsSystem32") then
[Code].....
I have an array with wild character at the end. I use the value in this array in Select statement. Some times the array has same values. I want only distinct values in the array before the select statment.
Code:
for i=0 to count
select * from tbl where item like ' " & itemname(i) & "'
next
I need distinct element in itemname array.
I'm creating a db tool for automatic database repairs i have the following sql statement:
[code]...
Where the 'BrokenDbFile' is the location of the db where data has to come from and the 'sqlString' = the access statement.it gives a error when i put it in a try the exception is of type OleDbExceprion and de message text is "De instructie INSERT bevat een syntaxisfout" in Dutch Where it says that the syntax of the INSERT INTO is not correct. but when i paste the string from my inteli snce into access it works fine.
I'm building a dynamic query in my ASP.NET MVC project by the following: [code]Where basically the strWhere is a string that I build up a dynamic filter depending on what the user selects to search on.This works great until I need to add a date comparison to the date clause.[code]Which will end up as:"SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos Where userInfos.BirthDate <= 10/09/1992"..But when i try to execute the query with the ToList whenever a date is in the where string i get the following error:The argument types 'Edm.DateTime' and 'Edm.Int32' are incompatible for this operation. Near WHERE predicate, line 1, column 103.
View 2 RepliesI'm working through a programming project, which should be quite easy but im stuck right at the start (relatively new to VB). I have a textbox (named textbox1) and a checkbox next to it. I want the checkbox to be ticked (.checked) when ever the value in the textbox is between 1 and 100, the checkbox to not be ticked when theres nothing in there, and for it not to be ticked when the value is < 1 and > 100. Figured a case statement would be the easiest. (this piece of code is just a validation check, basically in the real thing the user would have to enter in a value between 1 and a given number, and need to tell the user if that value is valid).
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Select Case TextBox1.Text
Case Is = ""
[code]....
But what happens is, if I enter in the value 1 in the textbox, the checkbox becomes ticked, as it should. It tick with any values between 2 and 99 (any number not starting with 1) but the checkbox becomes ticked again when the value is 100. For any other range, say 1 to 70, the checkbox would be ticked if the value in the textbox is 1 to 7, or any number starting with 1 or 7, but not 8 or 9, or any number starting with 8 or 9.
I have a form with a list box The code loops through a Tables' row and gives me only one of each of the catergories. If more than one of a perticular item exist it only displays one. i would like to use Select Distinct instead just because it would cut down on the time it would take to read the Rows items and still give me the same results I just am not sure how to write it.
[Code]...
upgrade my DAO code to ADO? The following code worked fine in VB.Net for Interop Access 2007.
View 1 RepliesSee
Public Class Form1
Dim sql As String
Dim conn As New OleDb.OleDbConnection
Dim da As New OleDb.OleDbDataAdapter
[CODE]...
The problem, The INSERT statement works fine in the form load but not in the button click event?
I've built an app that uses the Groupwise (email client) API. It will perform as expected when I hit F5,everything is good, when I use ctrl+F5 it fails with an access violation (see below). I tried to used JIT debugging, but I never get to see the source code, just the assembly. The app *never* fails with this error when run in the IDE.Groupwise documentation is sparse.[code]...
View 7 RepliesI receive this error: [code]whenever I run a query via a visual studio project in vb.net. If I run the same query via Open Office Base, it takes a long time but it works. How do I go about troubleshooting whats causing this?
View 2 RepliesI have an application which connects to a database, retrieves a username from a user's table and matches it against the username retrieved with System.Security.Principal.WindowsIdentity.GetCurrent.Name.On my localhost, everything works fine. The database exists on the development server but the application lies on my localhost. All of my authorization and authentication techniques are running smoothly.However, when I publish my application to the development server, I'm faced with the following error.Cannot open database requested in login 'databaseName'. Login fails.Login failed for user 'DevelopmentServerNameASPNET'.
[Code]...
I am tryin to access a 2003 Microsoft Access Database using VB in an OleDb format. The database file name is Billings.mdb and there are 3 tables in the file the one I am trying to access is tbl Clients. When debugging the program I continue to get an Unhandled error point to the FROM part of the SELECT statement. It continues to occur regardless of my attempts to vary the input.
In the program my connection statement is as follows:
Dim connectionString As String = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = C:Documents and SettingsTrialsBillings.mdb"
The Select statement is as follows:
Dim selectStatement As String = "SELECT ID, ..., PB " & "FROM tbl_Clients " & "WHERE ID = ?sngID"
I am trying to retrieve information from one row that has four columns(name,company,address,phone) so that once it is displayed in 4 different textboxes, I can transfer to a word document.
View 2 RepliesPublic Class Customer
Private m_CID As Integer
Private m_FirstName As String
[Code].....
My question is that do I need to add an OID in customer class or I can directly send a query to customer table when ever an order is made.
[code] BUT, in code, watching the locals window for rec.EOF and rec.BOF both return true and therefore no results.Why does this work in VB Query Designer but not in code? If I remove the WHERE statement than the results return fine as expected.But its returning ALL of the results which is no good.
View 1 RepliesI can't quite figure out why this Linq Statement isn't working as i would expect:
[Code]....
I would assume that this would create a new collection of anonymous types, that would be distinct. Instead it creates a collection the size of the "ThisParentCollection" with duplicate "MyAnonymousType" in it (duplicate id's).
I have a problem running a simple query: "select distinct payee from table". This query is set up in the dataset and is called GetAllPayee.My code goes something like:
Dim dtSources as new Datatable
dtSources =
Me.TblExpenseRecordsTableAdapter.GetAllPayee
Upon running I get the error:Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.The problem is, I haven't set ANY constraints on either the new datatable, or the dataset table (tblExpenseRecords). i.e. there is no primary key etc.
Can someone please help me out with this problem?I have no problems running the query through the wizard where I can view the results successfully.
Is it possible to select distinct in the rowfilter method?
I have something like:
VB
'Create a data view so we can get each items name
Dim dvwItemNames As New DataView(dtlItems)
dvwItemNames.RowFilter() = "SELECT DISTINCT Name"
This gives me a syntax error exception. I tried just "Distinct Name" but this had the same result. "Name" is the column name. I would rather do it this way then looping through the entire table and putting the name in an array list or something.
I have a single columned datatable inside a single tabled dataset.I just want to convert this dataset to distinct rows. Here is my code, it gives compile error '.' expected.
Dim query = _
From email In ds.Tables(0) _
Select email.Field<string>("Email").Distinct()
EDIT: I changed to (Of String) and it works... BUT NOW 'query' is an ienumerable collection of characters... not a datatable... so how do I convert back easily without manually doing a loop?
I've a collection with the data like this.[code]...
how can select the distinct data using linq?
I have set up a class to hold song information - artist, title. I then created another class that containg a BindingList(of clsSong). I use this binding list to create a data object that can be used to create an RDLC report. This all works. Now I am trying to remove duplicates from the binding list so they don't show up twice on the report. I can't figure out how to get a distinct list for my report. [code]...
View 3 RepliesI used the following code to select unique values from the database [code]And it does not retrieve the unique values, it remains the same. But if I use only SELECT DISTINCT Author FROM tblBook it works fine.
View 3 RepliesI'm pretty new in Linq and the first problem I've is to select the Distinct values from a ObservableCollection( Of T)
I've tried with[code]...
I'm using LINQ to SQL to select some columns from one table. I want to get rid of the duplicate result also.
Dim customer = (From cus In db.Customers Select cus.CustomerId, cus.CustomerName).Distinct
Result:
1 David
2 James
1 David
3 Smith
2 James
5 Joe
Wanted result:
1 David
2 James
3 Smith
5 Joe
Can anyone show me how to get the wanted result?
I am trying to get distinct rows based on multiple columns (attribute1_name, attribute2_name) and get datarows from datatable using Linq-to-Dataset.[code]How to do thin Linq-to-dataset?
View 3 RepliesIs it possible to select distinct values from a DataTable?
View 5 RepliesI have an EnumerableRowCollection that looks like the following:
VendorCode | GroupType | Variance
01165 G .16
01165 G .16
01165 CH .16
01165 CH .18
07754 G .25
07754 G .25
07754 G .39
Essentially, this is a massive list of vendor codes, their groups, and price variances. I need to compose a query that will create a distinct list vendor codes and group types. The catch, however, is that I need to evaluate all of the variances associated with that particular VendorCode/GroupType to see if they are all the same - it they are not, I need to return some way of signifying that the group has a "custom" variance, otherwise it needs to return the value (ie: if they are all .16, then return .16, if there are multiple values, return "custom")
The result would look like this, based off of the list I showed above.
VendorCode | GroupType | Variance
01165 G .16
01165 CH custom
07754 G custom
I have no trouble getting a distinct list of VendorCode/GroupType - this is what I have so far:
Dim distinctList = From q In query Select q.VendorCode, q.GroupType, (evaluated q.Variance here?) Distinct
(where "query" is an EnumerableRowCollection(Of (anonymous type)))
I'm at a loss, though, on how to evaluate the variance property to get the result that I need?
My particular example is fairly complex but I think the concept would apply equally to something like a logging system so I'll use that instead for ease of explanation. It's a ficticious example, please don't harp on or agonise over what is achitectually, programatically or morally wrong with the example itself
[Code]...
I don't want to 'cheat' and resort to a long-winded way of doing it when performance isn't critical here and I'm moderately confident it can be done in a single LINQ statement.
