What is wrong here?

  • Thread starter Thread starter Shelly
  • Start date Start date
S

Shelly

I get an error that the input string is not in the right format. Here is
the result:

Here is the partial code:
Dim sqlConn As New SqlConnection(SqlDataSource1.ConnectionString)
Dim query As String = "SELECT * FROM Agent WHERE accountNumber="
query += "@accountNumber AND agentID=@agentID AND authCode=@authCode"
Dim sqlComm As New SqlCommand(query, sqlConn)
sqlComm.Parameters.Add("@accountNumber", SqlDbType.Int).Value =
accountNumber.Text
sqlComm.Parameters.Add("@agentID", SqlDbType.Int).Value = agentID.Text
sqlComm.Parameters.Add("@authCode", SqlDbType.NChar).Value =
authCode.Text

Here is what sql.Comm has:
SELECT * FROM Agent WHERE accountNumber=@accountNumber AND
agentID=@agentID AND authCode=@authCode

Isn't the .Add supposed to replace the @ stuff witht he values?

I had been using StringBuilder to create the query text, but this looked so
much neater. What is wrong?

Shelly
 
Are you certain the data in your text fields can be converted to the
appropriate types? i.e. you don't have non-numeric characters in
accountNumber.Text? Have you tried hardcoding the values instead of reading
them from controls?
 
nothing is wrong.

with parametrized queries, sqlserver replaces the parameters with the
passed parameters just like a stored proc call. this means parameters
can only be used where a parameter is legal (for example the tablename
can not be a parameter.

-- bruce (sqlwork.com)
 
Shelly said:
I get an error that the input string is not in the right format.
Click to expand...

That means that you put strings in the parameters that can't be
converted to numbers.

On what line do you get the error?
Here is
the result:

Here is the partial code:
Dim sqlConn As New SqlConnection(SqlDataSource1.ConnectionString)
Dim query As String = "SELECT * FROM Agent WHERE accountNumber="
query += "@accountNumber AND agentID=@agentID AND authCode=@authCode"
Dim sqlComm As New SqlCommand(query, sqlConn)
sqlComm.Parameters.Add("@accountNumber", SqlDbType.Int).Value =
accountNumber.Text
sqlComm.Parameters.Add("@agentID", SqlDbType.Int).Value = agentID.Text
sqlComm.Parameters.Add("@authCode", SqlDbType.NChar).Value =
authCode.Text

Here is what sql.Comm has:
SELECT * FROM Agent WHERE accountNumber=@accountNumber AND
agentID=@agentID AND authCode=@authCode

Isn't the .Add supposed to replace the @ stuff witht he values?
Click to expand...

No. The parameters are sent along with the query to the database.
I had been using StringBuilder to create the query text, but this looked so
much neater.
Click to expand...

And a lot safer.
 
James said:
Are you certain the data in your text fields can be converted to the
appropriate types? i.e. you don't have non-numeric characters in
accountNumber.Text? Have you tried hardcoding the values instead of
reading them from controls?
Click to expand...

Yes, I can, and have, hardcoded them using StringBuilder. This looked like
a neater and cooler way to to do it, so I tried it and want to learn what I
did wrong. (There are no non-numeric characters).

Shelly
 
bruce barker said:
nothing is wrong.

with parametrized queries, sqlserver replaces the parameters with the
passed parameters just like a stored proc call. this means parameters can
only be used where a parameter is legal (for example the tablename can not
be a parameter.

-- bruce (sqlwork.com)
Click to expand...

OK, I don't use a parameter for the name. Why isn't this working?
 
Göran Andersson said:
That means that you put strings in the parameters that can't be converted
to numbers.
Click to expand...

The strings are "1000" for the accountNumber and "1" for the agentID and
"testit" for authCode. I get an error on the next line where I say:
On what line do you get the error?
Click to expand...

The next lines after what I showed before are:
sqlConn.Open()
Dim reader as SqlDataReader
reader = sqlComm.ExecuteReader

It is at this last line that I get the error, when I attempt to execute the
query.

Shelly
 
Shelly said:
The strings are "1000" for the accountNumber and "1" for the agentID and
"testit" for authCode.
Click to expand...

Then try to convert the values yourself instead of leaving it to the
database driver to figure it out:

sqlComm.Parameters.Add("@accountNumber", SqlDbType.Int).Value =
Int32.Parse(accountNumber.Text)
sqlComm.Parameters.Add("@agentID", SqlDbType.Int).Value =
Int32.Parse(agentID.Text)
 
Lets start from scratch.

Dim query As String = "SELECT * FROM Agent WHERE accountNumber=" & _
"@accountNumber AND agentID=@agentID AND authCode=@authCode"
Dim sqlConn As New SqlConnection(SqlDataSource1.ConnectionString)
Dim sqlComm As New SqlCommand(query, sqlConn)
SqlComm.CommandType = CommandType.StoredProcedure '<---- Important, this
maybe the issue.
sqlComm.Parameters.Add("@accountNumber", SqlDbType.Int).Value =
cint(accountNumber.Text)
sqlComm.Parameters.Add("@agentID", SqlDbType.Int).Value = cint(agentID.Text)
sqlComm.Parameters.Add("@authCode", SqlDbType.NChar).Value = authCode.Text
sqlConn.Open()
Dim reader as SqlDataReader = sqlComm.ExecuteReader
While Reader.Read()
' Do Stuff
Reader.Close
Connection.close
 
I played around and played around with it and finally got it to work. Here
is the final code that did work (and not with a stored procedure). Also, I
had trouble with CInt, but Convert.ToInt32 worked.

Protected Function ValidateAgent(ByVal acct As Integer, ByVal agent As
Integer, ByVal pwd As String) As Boolean
Dim sqlConn As New SqlConnection(SqlDataSource1.ConnectionString)
Dim query As String = "SELECT * FROM Agent WHERE accountNumber="
query += "@accountNumber AND agentID=@agentID AND authCode=@authCode"
Dim sqlComm As New SqlCommand(query, sqlConn)
sqlComm.Parameters.Add("@accountNumber", SqlDbType.Int).Value = acct
sqlComm.Parameters.Add("@agentID", SqlDbType.Int).Value = agent
sqlComm.Parameters.Add("@authCode", SqlDbType.NChar).Value = pwd
sqlConn.Open()
Dim reader As SqlDataReader
reader = sqlComm.ExecuteReader
Dim val As Boolean
If (reader.HasRows) Then
val = True
Else
val = False
End If
reader.Close()
sqlConn.Close()
Return val
End Function
 
Alexander said:
Lets start from scratch.

Dim query As String = "SELECT * FROM Agent WHERE accountNumber=" & _
"@accountNumber AND agentID=@agentID AND authCode=@authCode"
Dim sqlConn As New SqlConnection(SqlDataSource1.ConnectionString)
Dim sqlComm As New SqlCommand(query, sqlConn)
SqlComm.CommandType = CommandType.StoredProcedure '<---- Important, this
maybe the issue.
Click to expand...

It should be CommandType.Text, not CommandType.StoredProcedure, as it's
an SQL query, not a stored procedure.

The default for the property is CommandType.Text, so you don't have to
specify it.
 
Back
Top