Sql vs Oledb error

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

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

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
>
>
>
>
>
>
>

On Sep 20, 12:22 pm, MikeS <Mi...@discussions.microsoft.com> wrote:
> 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.