OdbcCommand not taking
-
I’m having troubles with the following code: txtSelect.Text = “Select System_id From [DFIParmsHTS].[docsadm].[ExtGroup] where CustNumber = _CustomerNumber” OdbcCommand cmdSelect= new OdbcCommand(txtSelect.Text, conn); cmdSelect.CommandType=CommandType.Text; cmdSelect.Parameters.Add("_CustomerNumber", vfx.TNM); dr = cmdSelect.ExecuteReader(); Throws SQLSRV32.DLL - ERROR [42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '_CustomerNumber' No kidding _CustomerNumber is an Invalid Column name. I’m trying to replace it with vfx.TNM. If I look at the Command object in the watch window Parameters.items[0].parametername = “_CustomerNumber” and Parameters.items[0].Value = “000122” So why isn’t it getting to the db that way. I’ve also tried txtSelect.Text = “Select System_id From [DFIParmsHTS].[docsadm].[ExtGroup] where CustNumber = @CustomerNumber” OdbcCommand cmdSelect= new OdbcCommand(txtSelect.Text, conn); cmdSelect.CommandType=CommandType.Text; cmdSelect.Parameters.Add("@CustomerNumber", vfx.TNM); dr = cmdSelect.ExecuteReader(); Throws SQLSRV32.DLL - ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@CustomerNumber' So to me it looks like the Command isn’t committing the changes of the parameters to the query. That made me try: txtSelect.Text = “Select System_id From [DFIParmsHTS].[docsadm].[ExtGroup] where CustNumber = @CustomerNumber” OdbcCommand cmdSelect= new OdbcCommand(txtSelect.Text, conn); cmdSelect.CommandType=CommandType.Text; cmdSelect.Parameters.Add("@CustomerNumber", vfx.TNM); cmdSelect.Prepare(); dr = cmdSelect.ExecuteReader(); And that didn’t work either. Any help is appreciated, Thanks. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
First question would be why are you using OdbcCommand rather than SqlCommand? It appears you are connecting to a Sql Server database.
-
I’m having troubles with the following code: txtSelect.Text = “Select System_id From [DFIParmsHTS].[docsadm].[ExtGroup] where CustNumber = _CustomerNumber” OdbcCommand cmdSelect= new OdbcCommand(txtSelect.Text, conn); cmdSelect.CommandType=CommandType.Text; cmdSelect.Parameters.Add("_CustomerNumber", vfx.TNM); dr = cmdSelect.ExecuteReader(); Throws SQLSRV32.DLL - ERROR [42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '_CustomerNumber' No kidding _CustomerNumber is an Invalid Column name. I’m trying to replace it with vfx.TNM. If I look at the Command object in the watch window Parameters.items[0].parametername = “_CustomerNumber” and Parameters.items[0].Value = “000122” So why isn’t it getting to the db that way. I’ve also tried txtSelect.Text = “Select System_id From [DFIParmsHTS].[docsadm].[ExtGroup] where CustNumber = @CustomerNumber” OdbcCommand cmdSelect= new OdbcCommand(txtSelect.Text, conn); cmdSelect.CommandType=CommandType.Text; cmdSelect.Parameters.Add("@CustomerNumber", vfx.TNM); dr = cmdSelect.ExecuteReader(); Throws SQLSRV32.DLL - ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@CustomerNumber' So to me it looks like the Command isn’t committing the changes of the parameters to the query. That made me try: txtSelect.Text = “Select System_id From [DFIParmsHTS].[docsadm].[ExtGroup] where CustNumber = @CustomerNumber” OdbcCommand cmdSelect= new OdbcCommand(txtSelect.Text, conn); cmdSelect.CommandType=CommandType.Text; cmdSelect.Parameters.Add("@CustomerNumber", vfx.TNM); cmdSelect.Prepare(); dr = cmdSelect.ExecuteReader(); And that didn’t work either. Any help is appreciated, Thanks. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
-
First question would be why are you using OdbcCommand rather than SqlCommand? It appears you are connecting to a Sql Server database.
Well some sites use MS SQL others use Postgrsql. When you write code that is flexible and not just MS you can't always take the easy way. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
-
Parameters in ODBC don't have names. Use "?" in the query to identify the parameters, and add the parameters to the parameter collection in the same order as they appear in the query. --- b { font-weight: normal; }
Cool Thanks for the help. I Guess I'm going to just to Replace() on the input string then. The attraction of using parameters was that it allowed a bit of latitude in how the sql strings where made. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
-
Cool Thanks for the help. I Guess I'm going to just to Replace() on the input string then. The attraction of using parameters was that it allowed a bit of latitude in how the sql strings where made. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
HahnTech wrote:
I Guess I'm going to just to Replace() on the input string then.
Why? Just use '?'
Select System_id From [DFIParmsHTS].[docsadm].[ExtGroup] where CustNumber = ?thencmdSelect.Parameters.Add("", vfx.TNM);"Just about every question you've asked over the last 3-4 days has been "urgent". Perhaps a little planning would be helpful?"
Colin Angus Mackay in the C# forumled mike
-
Well some sites use MS SQL others use Postgrsql. When you write code that is flexible and not just MS you can't always take the easy way. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
True enough. You didn't mention anything about the databases you were using. From the evidence you provided it seemed only SQL Server. You might want to look at the Microsoft Enterprise Library and the Data Application block to assist in writing a more generic data access layer.
-
HahnTech wrote:
I Guess I'm going to just to Replace() on the input string then.
Why? Just use '?'
Select System_id From [DFIParmsHTS].[docsadm].[ExtGroup] where CustNumber = ?thencmdSelect.Parameters.Add("", vfx.TNM);"Just about every question you've asked over the last 3-4 days has been "urgent". Perhaps a little planning would be helpful?"
Colin Angus Mackay in the C# forumled mike
Yeah that would work Great if I was always only going to use one where clause or if this was the only sql statement in the program. But tomorrow or a week from now, myself or some one else will have to edit the sql to return a smaller range and forget that its ordinal specific then spend 2h trying to figure out why its not working. Replace() is ordinal unspecific and works all the time. I envy all you programmers that get to use a static DB schema. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
-
Yeah that would work Great if I was always only going to use one where clause or if this was the only sql statement in the program. But tomorrow or a week from now, myself or some one else will have to edit the sql to return a smaller range and forget that its ordinal specific then spend 2h trying to figure out why its not working. Replace() is ordinal unspecific and works all the time. I envy all you programmers that get to use a static DB schema. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
-
Well some sites use MS SQL others use Postgrsql. When you write code that is flexible and not just MS you can't always take the easy way. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
HahnTech wrote:
Well some sites use MS SQL others use Postgrsql. When you write code that is flexible and not just MS you can't always take the easy way
Ummm..... You are going to have to write different SQL queries anyway for each database because of the differences in the way SQL is implemented in each database. If you write a proper DAL (Data Access Layer) you can swap in and out the DAL depending on the datbase you are using. The rest of the application uses interface(s) to the DAL, so both have the same public methods. However, inside each of the DAL classes the datbase code is optimised for the appropriate database.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Cool Thanks for the help. I Guess I'm going to just to Replace() on the input string then. The attraction of using parameters was that it allowed a bit of latitude in how the sql strings where made. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
-
Stored Procedures
"Just about every question you've asked over the last 3-4 days has been "urgent". Perhaps a little planning would be helpful?"
Colin Angus Mackay in the C# forumled mike
Too many sites to manage and a moving Db schema make them too hard to manage. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net
-
True enough. You didn't mention anything about the databases you were using. From the evidence you provided it seemed only SQL Server. You might want to look at the Microsoft Enterprise Library and the Data Application block to assist in writing a more generic data access layer.
Thanks for the link. I need all the help i can get with this two DBS thing. Ronald Hahn, CNT - Computer Engineering Technologist New Technologies Analyst HahnTech Affiliated With Code Constructors Edmonton, Alberta, Canada Email: rhahn82@telus.net