By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,636 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,636 IT Pros & Developers. It's quick & easy.

Sql vs Oledb error

P: n/a
Below is the code for 2 separate functions. The first one gets data from an
Access database and works fine. The second one is almost a duplicate except
it gets data from an SQL database. It works fine if I insert the actual
value for the criteria. In other words, if I insert "mscott" in the place of
"txtData.Text", it will return the expected results. But if I use the
criteria from my input field, I get the following error:

"Invalid column name 'mscott'." (mscott = value from text box)

Can anyone tell me how to correct this and why is this different in SQL?

************************************************** ****
Private Sub GetDataAccess()
Using conn As New OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= _
C:\Data\Access\ItemMaster.mdb")
Using com As OleDb.OleDbCommand = conn.CreateCommand()
conn.Open()
com.CommandType = Data.CommandType.Text
com.CommandText = "Select Dept From Security Where UserID =
" & _
txtData.Text
com.Parameters.Add("UserID", OleDb.OleDbType.VarChar).Value
= _
txtData.Text
Dim strDept As String = String.Empty

Try
strDept = com.ExecuteScalar().ToString()
Catch ex As NullReferenceException
MsgBox("UserID not found....")
txtData.Text = String.Empty
End Try
lblData.Text = strDept
conn.Close()
End Using
End Using
End Sub

************************************************** *****
Private Sub GetDataSQL1()
Using conn As New SqlClient.SqlConnection _
("Server=KIN-SSQL02;" & "Database=FTQ;" & "User ID=abc;" _
& "Password=123;")
Using com As SqlClient.SqlCommand = conn.CreateCommand()
conn.Open()
com.CommandType = Data.CommandType.Text
com.CommandText = "Select Dept from Security Where UserID =
" & _
txtData.Text
com.Parameters.Add("UserID", SqlDbType.VarChar).Value = _
txtData.Text
Dim strData As String = String.Empty

Try
strData = com.ExecuteScalar().ToString()
Catch ex As NullReferenceException
MsgBox("User ID not found....")
txtData.Text = String.Empty
End Try
lblData.Text = strData
conn.Close()
End Using
End Using

End Sub

************************************************** **

Thanks in advance,

Mike

Sep 20 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Try changing it to this:

//////////////////////////////////
Private Sub GetDataSQL1()
Using conn As New SqlClient.SqlConnection("Server=KIN-
SSQL02;Database=FTQ;User ID=abc;Password=123;"), _
com As SqlClient.SqlCommand = conn.CreateCommand()
conn.Open()
com.CommandType = Data.CommandType.Text
com.CommandText = "Select Dept from Security Where UserID =
@UserId"
com.Parameters.Add("@UserID", SqlDbType.VarChar).Value =
txtData.Text
Dim strData As String = String.Empty

Try
strData = com.ExecuteScalar().ToString()
Catch ex As NullReferenceException
MsgBox("User ID not found....")
txtData.Text = String.Empty
End Try
lblData.Text = strData
End Using
End Sub
////////////////////////////
Let me know how it works.

Thanks,

Seth Rowe


Sep 20 '07 #2

P: n/a
Thanks Seth....that works great.

"rowe_newsgroups" wrote:
Try changing it to this:

//////////////////////////////////
Private Sub GetDataSQL1()
Using conn As New SqlClient.SqlConnection("Server=KIN-
SSQL02;Database=FTQ;User ID=abc;Password=123;"), _
com As SqlClient.SqlCommand = conn.CreateCommand()
conn.Open()
com.CommandType = Data.CommandType.Text
com.CommandText = "Select Dept from Security Where UserID =
@UserId"
com.Parameters.Add("@UserID", SqlDbType.VarChar).Value =
txtData.Text
Dim strData As String = String.Empty

Try
strData = com.ExecuteScalar().ToString()
Catch ex As NullReferenceException
MsgBox("User ID not found....")
txtData.Text = String.Empty
End Try
lblData.Text = strData
End Using
End Sub
////////////////////////////
Let me know how it works.

Thanks,

Seth Rowe


Sep 20 '07 #3

P: n/a
On Sep 20, 12:22 pm, MikeS <Mi...@discussions.microsoft.comwrote:
Below is the code for 2 separate functions. The first one gets data from an
Access database and works fine. The second one is almost a duplicate except
it gets data from an SQL database. It works fine if I insert the actual
value for the criteria. In other words, if I insert "mscott" in the place of
"txtData.Text", it will return the expected results. But if I use the
criteria from my input field, I get the following error:

"Invalid column name 'mscott'." (mscott = value from text box)

Can anyone tell me how to correct this and why is this different in SQL?

************************************************** ****
Private Sub GetDataAccess()
Using conn As New OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= _
C:\Data\Access\ItemMaster.mdb")
Using com As OleDb.OleDbCommand = conn.CreateCommand()
conn.Open()
com.CommandType = Data.CommandType.Text
com.CommandText = "Select Dept From Security Where UserID =
" & _
txtData.Text
com.Parameters.Add("UserID", OleDb.OleDbType.VarChar).Value
= _
txtData.Text
Dim strDept As String = String.Empty

Try
strDept = com.ExecuteScalar().ToString()
Catch ex As NullReferenceException
MsgBox("UserID not found....")
txtData.Text = String.Empty
End Try
lblData.Text = strDept
conn.Close()
End Using
End Using
End Sub

************************************************** *****
Private Sub GetDataSQL1()
Using conn As New SqlClient.SqlConnection _
("Server=KIN-SSQL02;" & "Database=FTQ;" & "User ID=abc;" _
& "Password=123;")
Using com As SqlClient.SqlCommand = conn.CreateCommand()
conn.Open()
com.CommandType = Data.CommandType.Text
com.CommandText = "Select Dept from Security Where UserID =
" & _
txtData.Text
com.Parameters.Add("UserID", SqlDbType.VarChar).Value = _
txtData.Text
Dim strData As String = String.Empty

Try
strData = com.ExecuteScalar().ToString()
Catch ex As NullReferenceException
MsgBox("User ID not found....")
txtData.Text = String.Empty
End Try
lblData.Text = strData
conn.Close()
End Using
End Using

End Sub

************************************************** **

Thanks in advance,

Mike
If the column UserID is a varchar, then the value from txtData.Text
must be wrapped in single quotes.

The only difference between the way you were trying it and the way a
suggested workaround works, is the workaround specifies the value as a
parameter, where the "wrapping in single quotes" is not applicable.

Sep 20 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.