Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to store data from a textbox to sql DB

Posted on 2009-02-12
4
Medium Priority
?
1,348 Views
Last Modified: 2013-12-20
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

0
Comment
Question by:tehmole
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 23627688
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
 

Author Comment

by:tehmole
ID: 23661777
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 23662169
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
 

Author Comment

by:tehmole
ID: 23874924
Sorry for the delay. I was out of the country and counldnt respond till now. Thank you for your solution.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month10 days, 16 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question