Sql vs Oledb error

[Guest]

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

 

[rowe_newsgroups]

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

 

[Guest]

Thanks Seth....that works great.

 

[zacks]

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.