Friday, March 23, 2012

How to reference a text box in an insert statement

I am trying to take a value from at textbox and insert into a database. I think I need to convert the text to an integer - but I keep getting an error message saying I'm not allowed to use column names. Basically, I need to figure out how to take user input from these tet boxes and insert them into a database. Any ideas would be welcomed. the exact error is at the bottom the code.

Here is the code:

Imports System.Data
Imports System.Data.SqlClient
Partial Class County_ConversionTest
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim v1 As Integer
Dim v2 As Integer
'Dim v3 As Integer
v1 = Integer.Parse(TextBox1.Text)
v2 = Integer.Parse(TextBox2.Text)
TextBox3.Text = (v1 + v2)

End Sub

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim strconn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\HCBS\App_Data\DivAging_Data.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim cnnDivAging As SqlConnection = New SqlConnection(strconn)
Dim v1 As Integer
Dim v2 As Integer
Dim v3 As Integer
v1 = CInt(TextBox1.Text)
v2 = CInt(TextBox2.Text)
v3 = CInt(TextBox3.Text)


Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George',v1,v2,v3)"

Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging)
cmdUpdates.Connection = cnnDivAging
cnnDivAging.Open()
cmdUpdates.CommandType = CommandType.Text
cmdUpdates.ExecuteNonQuery()
cnnDivAging.Close()
End Sub
End Class

Here is the error message -

The name "v1" is not permitted in this context. Valid expressions areconstants, constant expressions, and (in some contexts) variables.Column names are not permitted.hey jcahill,
try this:

Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George'," & v1 & "," & v2 & "," v3 & ")"

v1, v2, v3 are not there in sql, you just want the values in the sql.
Hope it helps.|||

You should get in the habit of doing it the right way:

Dim strsql AS String="INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George',@.v1,@.v2,@.v3)"
Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging)
' cmdUpdates.Connection = cnnDivAging -- Redundant
cmdUpdates.Parameters.Add("@.v1",sqldbtype.int32).value=v1
cmdUpdates.Parameters.Add("@.v2",sqldbtype.int32).value=v2
cmdUpdates.Parameters.Add("@.v3",sqldbtype.int32).value=v3
cnnDivAging.Open()
' cmdUpdates.CommandType = CommandType.Text -- Redundant and should be done before the open to minimize the amount of time the connection is "open"
cmdUpdates.ExecuteNonQuery()
cnnDivAging.Close()

sql

No comments:

Post a Comment