how to store data from a textbox to sql DB

hi, messing around with VB 2005 for a project...

i'm trying to figure out how to populate my DB by users entering data in a textbox.  By clicking a button will update the DB with the new data entered.

please look at my code, i dont know what i'm doing wrong or right...

thank you in advance....
Imports System.Data.SqlClient
 
 
Public Class Form1
    Dim mycon As SqlConnection
    Dim myad As SqlDataAdapter
    Dim dt As DataTable
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
 
 
        'establish connection to datatable
        mycon = New SqlConnection
        mycon.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\something.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"
        mycon.Open()
 
        'create adapter to the connection
        myad = New SqlDataAdapter
        myad.SelectCommand = New SqlCommand
        myad.SelectCommand.CommandText = "select * from concerts"
        myad.SelectCommand.Connection = mycon
 
        dt = New DataTable
        myad.Fill(dt)
 
        DataGridView1.DataSource = dt
 
 
        ListBox1.DataSource = dt
        ListBox1.DisplayMember = "CONCERTS"
 
 
'update commands
        myad.UpdateCommand = New SqlCommand
        myad.UpdateCommand.CommandText = "update members set CONCERTS=@CN, SEATS=@ST, ID=@ID"
        myad.UpdateCommand.Parameters.Add("@CN", SqlDbType.VarChar, 50, "CONCERTS")
        myad.UpdateCommand.Parameters.Add("@ST", SqlDbType.Int, 50, "SEATS")
        myad.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 50, "ID")
 
        myad.UpdateCommand.Connection = mycon
 
        'insert commands
 
        myad.InsertCommand = New SqlCommand
        myad.InsertCommand.Connection = mycon
        myad.InsertCommand.CommandText = "insert into concerts (CONCERTS) VALUES ('" & TextBox1.Text & "')"
 
        myad.InsertCommand.CommandText = "insert into Concerts (ID, CONCERTS, SEATS) VALUES (@ID, @CN, @ST)"
        myad.InsertCommand.Parameters.Add("@CN", SqlDbType.VarChar, 50, "CONCERTS")
        myad.InsertCommand.Parameters.Add("@ST", SqlDbType.Int, 50, "SEATS")
        myad.InsertCommand.Parameters.Add("@ID", SqlDbType.Int, 50, "ID")
 
 
 
End Sub
End Class

Open in new window

tehmoleAsked:
Who is Participating?
 
Daniel WilsonCommented:
If your Data Adapter is bound to a grid and is to be used to insert when you add something to the grid, then you need to work with its InsertCommand.

But if you're just adding based on what's in a text box (or text boxes), a Data Adapter is overkill.
0
 
Daniel WilsonCommented:
You're never executing those commands ... at least not in that button_click procedure.

Are you trying to modify (UPDATE) an existing record?  Or add (INSERT) a new one?

I'll give you an example of the latter ... you're close, but this is really much simpler.

Public Class Form1
    Dim mycon As SqlConnection
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        dim P as SqlParameter
 
        'establish connection to datatable
        mycon = New SqlConnection
        mycon.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\something.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"
        mycon.Open()
 
        cmd.CommandText = "insert into Concerts (ID, CONCERTS, SEATS) VALUES (@ID, @CN, @ST)"
        P = new SqlParameter("@CN", SqlDbType.VarChar, 50)
        P.Value = Text1.Text
        cmd.Parameters.Add (P)
        P = new SqlParameter("@ST", SqlDbType.Int)
        P.Value = Text2.Text
        cmd.Parameters.Add (P)
        
        P = new SqlParameter("@ID", SqlDbType.Int)
        P.Value = Text3.Text
        cmd.Parameters.Add (P)
 
        cmd.ExecuteNonQuery()
 
End Sub
End Class

Open in new window

0
 
tehmoleAuthor Commented:
i tried what you mentioned, i kind of sort of followed.... but still in the mist of confusion...

so .. i dont need myad.insertcommands ????


0
 
tehmoleAuthor Commented:
Sorry for the delay. I was out of the country and counldnt respond till now. Thank you for your solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.