ASP.NET 2.0: Cannot Convert VarChar to Int

Question

I have an ASP.Net form, where it grabs a value from a textbox:

<asp:TextBox ID="txtID" runat="server" maxlength=9></asp:TextBox>

The ID HAS to be 9 numbers.

After it grabs it, I want to insert it into a database (SQL Server 2005), so I build a parameterized string,

'The Query
cmd.CommandText = "insert into table (aid) values ('@aid')"
cmd.Connection = conn

'Grab the value
cmd.Parameters.add("@aid", SqlDBType.Int).value = txtID.text

'Execute!
cmd.Connection.Open()
cmd.ExecuteNonQuery

However, it doesn't let me. It keeps giving the following error message:

Conversion failed when converting the varchar value '@aid' to data type int.

So I've tried a variety of things:

cmd.Parameters.add("@aid", SqlDBType.Int).value = 999999999
cmd.Parameters.add("@aid", SqlDBType.Int).value = Convert.ToInt16(txtID.text)
cmd.Parameters.add("@aid", SqlDBType.Int).value = Convert.ToInt32(txtID.text)
cmd.Parameters.add("@aid", SqlDBType.Int).value = Convert.ToInt64(txtID.text)

Nothing works. Inside the database, the type is "int".

Any ideas?

Answer 1

Remove the quotes around @aid in your query, so that it looks like so:

cmd.CommandText = "insert into table (aid) values (@aid)"

Otherwise, you're sending the code mixed messages. Parameters are never enclosed in quotes. They are string literals if they're enclosed in quotes. Additionally, in pure SQL, numbers are not enclosed in quotes, but text values (varchar and the like) are. So, remove the quotes, and the parameter should have no issues being created.

Parameters aren't inserted straight into SQL wholesale. They're plopped in after SQL Server has parsed the query. Therefore, parameters should just be on their own, as they're taken as string literals if they aren't. The parameterization will take care to convert the parameter to the right data type for you. See this post for more on how parameters work behind the scenes.

Answer 2

Your sql query is the problem. You are trying to do

INSERT INTO TABLE(aid) VALUES('123456789')

You need to drop the quotes and do

INSERT INTO TABLE(aid) VALUES(123456789)