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