Problem with syntax with Wizard in a Select/Where statement
-
I am using vb.net 2003 for a stand alone windows program. I access an Access 2002 database file via Jet 4.0 and OleDbDataAdapter. The program is like a "check book" for commercial fishermen to track their landings in relation to the amount of "quota" that they have been assigned. The program is structured so a fisherman can enter multiple boat names and track each one seperately. To work on a particular boats account, the fisherman would select a boat name from a drop down list. The boat name is then captured and assigned the string variable name of "xid" and in a Module have this code:
Public xid as String
I used the DataAdapter Configuration Wizard to setup the data connection and assign the DataAdapter and DataSet. I went into Wizard generated code and manually entered a WHERE clause to filter the data for the selected boat. Doing this the program works fine. The code that works is:
... WHERE [Boat Name] = '" & xid & "'
. However, if I return to the Designer view I get an error message: The variable 'xid' is either undeclared or was never assigned. And the entire SELECT statement is erased. If I try letting the Wizard generate the WHERE clause when I enter:
... WHERE [Boat Name] = '" & xid & "'
, the code that is generated is has double double quotas as:
... WHERE [Boat Name] = '"" & xid & ""'
, and this does not show data on my form. I have gone through every possible combination of single quote and double quotes and can not get the wizard to produce the syntax that works. And if I manually edit the wizard generated code the program works, but I can not return to the Designer view. Does any one have a suggestion or solution?
-
I am using vb.net 2003 for a stand alone windows program. I access an Access 2002 database file via Jet 4.0 and OleDbDataAdapter. The program is like a "check book" for commercial fishermen to track their landings in relation to the amount of "quota" that they have been assigned. The program is structured so a fisherman can enter multiple boat names and track each one seperately. To work on a particular boats account, the fisherman would select a boat name from a drop down list. The boat name is then captured and assigned the string variable name of "xid" and in a Module have this code:
Public xid as String
I used the DataAdapter Configuration Wizard to setup the data connection and assign the DataAdapter and DataSet. I went into Wizard generated code and manually entered a WHERE clause to filter the data for the selected boat. Doing this the program works fine. The code that works is:
... WHERE [Boat Name] = '" & xid & "'
. However, if I return to the Designer view I get an error message: The variable 'xid' is either undeclared or was never assigned. And the entire SELECT statement is erased. If I try letting the Wizard generate the WHERE clause when I enter:
... WHERE [Boat Name] = '" & xid & "'
, the code that is generated is has double double quotas as:
... WHERE [Boat Name] = '"" & xid & ""'
, and this does not show data on my form. I have gone through every possible combination of single quote and double quotes and can not get the wizard to produce the syntax that works. And if I manually edit the wizard generated code the program works, but I can not return to the Designer view. Does any one have a suggestion or solution?
The problem is that you are trying to apply Access techniques on vb.net. The wizard does not, and will not ever understand that
WHERE [Boat Name] = '" & xid & "'means that you want to use a variable. Try writing the sql sentence in the wizard, and apply your variable like so:WHERE [Boat Name] = @xid. Then, in your vb.net code, before you call the DataAdapter.Fill method pass your variable's value to the adapter's parameter collection. Something like:DataAdapter.SelectCommand.Parameters.Add("@xid", NVarChar).Value = xid(you probably have to check for the correct syntax, but something along these lines anyway) As an added bonus you will be using the safe way to use parameterized queries (much less risk of an sql injection attack).My advice is free, and you may get what you paid for.
-
The problem is that you are trying to apply Access techniques on vb.net. The wizard does not, and will not ever understand that
WHERE [Boat Name] = '" & xid & "'means that you want to use a variable. Try writing the sql sentence in the wizard, and apply your variable like so:WHERE [Boat Name] = @xid. Then, in your vb.net code, before you call the DataAdapter.Fill method pass your variable's value to the adapter's parameter collection. Something like:DataAdapter.SelectCommand.Parameters.Add("@xid", NVarChar).Value = xid(you probably have to check for the correct syntax, but something along these lines anyway) As an added bonus you will be using the safe way to use parameterized queries (much less risk of an sql injection attack).My advice is free, and you may get what you paid for.
Thanks for the reply. I would like to get this to work properly. This is my first attempt at programing with vb.net and using a database. In the past all of my projects used text files for input an writing. This is very different. I made the change to the WHERE clause in the Wizard to
... WHERE [Boat Name] = @xid
Now in my form I have this code:
OleDbDataAdapter1.SelectCommand.Parameters.Add("@xid", NVarChar).Value = xid
OleDbDataAdapter1.Fill(QuotaPounds1.Balance)NVarChar is underlined with the explanation that it is not declared. If I edit this to VarChar it remains underlined with the explanation that it is not declared. If I make it Char, it is still underline but now the explanation is that it is "a Type and so is not a valid expression". I thought that the value in that spot was supposed to be a "Type". Any guidance you can provide on the syntax here would be most appreciated.
-
Thanks for the reply. I would like to get this to work properly. This is my first attempt at programing with vb.net and using a database. In the past all of my projects used text files for input an writing. This is very different. I made the change to the WHERE clause in the Wizard to
... WHERE [Boat Name] = @xid
Now in my form I have this code:
OleDbDataAdapter1.SelectCommand.Parameters.Add("@xid", NVarChar).Value = xid
OleDbDataAdapter1.Fill(QuotaPounds1.Balance)NVarChar is underlined with the explanation that it is not declared. If I edit this to VarChar it remains underlined with the explanation that it is not declared. If I make it Char, it is still underline but now the explanation is that it is "a Type and so is not a valid expression". I thought that the value in that spot was supposed to be a "Type". Any guidance you can provide on the syntax here would be most appreciated.
You can check from the intellisense what sort of object or value the code is expecting. In this case
SqlDbType.NVarChar. To see how it works, don't paste my code in there but type inSqlDbType.and see what the editor suggests. If the editor does not suggest anything, or the code still gets underlined, go all the way to the top of your code, above thePublic Class BlaBlaBlabit, and add:Imports System.Data.SqlClient(so this should be the very first line of code), and then try again.My advice is free, and you may get what you paid for.