Member Avatar for raphipps

I am new to aspx and sql. I have written some code with vb.net 2005 (visual studio) to do some calculations and poplate some textboxes on my web form.

I dont understand how to pass those values to a paramerter then use that value to write to my sql database. I found this code but the VALUES(.....................) in the single quotes are placed in the database and not the values themselves. So I tried without quotes and got strange error sayng the the value was some kind of column header. PLEASE HELP.

I am a novice programmer so please dont use to much technical jargon...thanks! :)

Dim oDR As System.Data.SqlClient.SqlDataReader
        Dim oCom As System.Data.SqlClient.SqlCommand
        Dim oConn As System.Data.SqlClient.SqlConnection

        Try
            oConn = New System.Data.SqlClient.SqlConnection("server=xxxxx.com; initial catalog=db303095; uid=dbo303095; pwd=XXXXXXXX")
            oConn.Open()
            oCom = New System.Data.SqlClient.SqlCommand()
            oCom.Connection = oConn
            'oCom.CommandText = "WRITE * To Children"

            Dim surnX As String = TextBox1.Text
            Dim middleX As String = TextBox2.Text
            Dim firstX As String = TextBox3.Text
            Dim ofstX As String = TextBox4.Text

*** so how to i pass the about variables to the values below ***

            'The first set are the column names.  Confirmed 18/11/09.
            oCom.CommandText = "INSERT INTO Children(First,Middle,Last,Ofsted) VALUES(@abc,'textbox1.text','textbox4.text','textbox2.text') "

            oCom.ExecuteNonQuery()

        Catch
            Response.Write("Error:" & Err.Description)
        Finally
            oDR = Nothing
            oCom = Nothing
            oConn.Close()
            oConn = Nothing
        End Try
Edited by kvprajapati because: Add [code] tags. Encase your code in: [code] and [/code]
  • 2 Contributors
  • 1 Reply
  • 226 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by kvprajapati
Member Avatar for kvprajapati

Use & (string concat) operator to form a query.

....
oCom.CommandText = "INSERT INTO Children(First,Middle,Last,Ofsted)  VALUES ('" & textbox1.text & "','" & textbox4.text & "','" & textbox2.text & "')"

oCom.ExecuteNonQuery()
....

I suggest you to use Parameterized Query.

Dim oCom As System.Data.SqlClient.SqlCommand
  Dim oConn As System.Data.SqlClient.SqlConnection
  Try
      oConn = New System.Data.SqlClient.SqlConnection("server=xxxxx.com; initial catalog=db303095; uid=dbo303095; pwd=XXXXXXXX")
      oCom = New System.Data.SqlClient.SqlCommand()
      oCom.Connection = oConn
      oCom.CommandText = "INSERT INTO Children(First,Middle,Last,Ofsted) VALUES (@first,@middle,@last,@ofsted)"

       oCmd.Parameters.Add(New SqlParameter("@first", SqlDbType.VarChar, 20)).Value = textbox1.Text
        oCmd.Parameters.Add(New SqlParameter("@middle", SqlDbType.VarChar, 20)).Value = TextBox2.Text
        oCmd.Parameters.Add(New SqlParameter("@last", SqlDbType.VarChar, 20)).Value = TextBox3.Text
        oCmd.Parameters.Add(New SqlParameter("@ofsted", SqlDbType.VarChar, 20)).Value = TextBox4.Text

      oConn.Open()
      oCom.ExecuteNonQuery()

      Catch
            Response.Write("Error:" & Err.Description)
        Finally
            oCom = Nothing
            oConn.Close()
            oConn = Nothing
      End Try
Edited by kvprajapati because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.