none
How to insert data into sql table using vb.net

    Question

  • I`m trying to create an applictaion where using a vb.net form u can enter data into sql tabel (i`m using visual studio 2005 and sql server 2005). I `m only able to get data from sql table using "data source configuration wizard".
    Please, will be greatly thankful!
    Monday, June 22, 2009 5:45 PM

Answers

  • Hi Vache,

    Here is the step-by-step procedure to INSERT record to SQL server:
    1. Create your VB.NET project.
    2. Include the following namespaces.
    Imports System.Data
    Imports System.Data.SqlClient

    The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.

    3. Declare and instantiate your SQLConnection object and Command object as shown below
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand

    4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.
    con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

    5. Invoke the Open Method to connect to SQL Server.
    con.Open()

    6. Set the connection of the command object.
    cmd.Connection = con

    7. Pass the INSERT SQL statement to the command object commandtext as shown below
    cmd.CommandText = "INSERT INTO table (field1, [field2, ... ]) VALUES (value1, [value2, ...])"

    8. Use the ExecuteNonQuery() method to run INSERT SQL that has no return value.
    cmd.ExecuteNonQuery()

    If you expect a return value use ExecuteScalar() instead. A good example of ExecuteScalar is to get the Identity column after inserting record
    (ExecuteScalar return the first column of the first row from the result).


    The full sample source code of inserting record to SQL Database:

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Try
    con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
    con.Open()
    cmd.Connection = con
    cmd.CommandText = "INSERT INTO table([field1], [field2]) VALUES([Value1], [Value2])"
    cmd.ExecuteNonQuery()

    Catch ex As Exception
    MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
    Finally
    con.Close()
    End Try
    Hope this answers your question. If you have any concern, please feel free to let me know.

    Thanks
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Vache Thursday, June 25, 2009 5:11 PM
    Thursday, June 25, 2009 3:44 AM
  • Hi Vache,

    You can try the following the code:

    "INSERT INTO tableName (field1) VALUES ('" & textBox1.text & "')"
    or

            Dim firstname, lastname As String
    
            firstname = Text1.Text
            lastname = Text2.Text
      
            cmd.CommandText = "INSERT INTO table([field1], [field2]) VALUES(@firstname, @lastname,)"
    

    Hope this helps.

    Thanks

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Edited by YiChun Chen Friday, June 26, 2009 1:51 AM typo
    • Marked as answer by Vache Tuesday, July 14, 2009 5:42 PM
    Friday, June 26, 2009 1:50 AM

All replies

  • If you drag a table from your datasources window unto your form, the wizard creates all the codes that you need to add data to, or update the table. When you drag the table unto the form, you'll see a number of buttons on top of the form (the navigation toolstrip). the navigation toolstrip has icons for add, delete, save, etc.

    Give it a trial and revert back.


    Only performance counts!
    Monday, June 22, 2009 6:04 PM
  • here is another way to enter data into a database.

    Dim row As DataRow
    row = DataSet.table.NewRow
    row("columnname")= x
    row("columnname")= y
    row("columnname")= z
    DataSet.table.Rows.Add(row)
    Monday, June 22, 2009 8:36 PM
  • Vache,

    i have a tutorial on my website which will explain how to work with database.  it is there if you would like to take a look.  it will explain more about what is going on behind the scenes of the drag and drop.

    Jeff
    FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial
    Monday, June 22, 2009 10:53 PM
  • First, thanks a lot for reply! And can i do the same actions (enter, delete, save etc.) out of the navigation tool strip, e.g. using command button or changing textbox, `cause the application i`m trying to create will have an interface of a banking software that has a standart form! Thanks...
    Tuesday, June 23, 2009 8:45 AM
  • E.g. i want to insert the data that is entered in a textbox or is included in combobox list. Thanks for care guys!
    • Proposed as answer by Anand Banse Thursday, January 09, 2014 11:54 AM
    Tuesday, June 23, 2009 8:49 AM
  • You can start from: http://www.homeandlearn.co.uk/NET/nets12p4.html


     One step at a time. If you follow the tutorial you can do all basic data operations. You can then proceed from there to more advance operations.
    Although, the example used Access DB, the difference is not much.

    Good luck.
    Only performance counts!
    Tuesday, June 23, 2009 10:59 AM
  • Hi Vache,

    Here is the step-by-step procedure to INSERT record to SQL server:
    1. Create your VB.NET project.
    2. Include the following namespaces.
    Imports System.Data
    Imports System.Data.SqlClient

    The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.

    3. Declare and instantiate your SQLConnection object and Command object as shown below
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand

    4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.
    con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

    5. Invoke the Open Method to connect to SQL Server.
    con.Open()

    6. Set the connection of the command object.
    cmd.Connection = con

    7. Pass the INSERT SQL statement to the command object commandtext as shown below
    cmd.CommandText = "INSERT INTO table (field1, [field2, ... ]) VALUES (value1, [value2, ...])"

    8. Use the ExecuteNonQuery() method to run INSERT SQL that has no return value.
    cmd.ExecuteNonQuery()

    If you expect a return value use ExecuteScalar() instead. A good example of ExecuteScalar is to get the Identity column after inserting record
    (ExecuteScalar return the first column of the first row from the result).


    The full sample source code of inserting record to SQL Database:

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Try
    con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
    con.Open()
    cmd.Connection = con
    cmd.CommandText = "INSERT INTO table([field1], [field2]) VALUES([Value1], [Value2])"
    cmd.ExecuteNonQuery()

    Catch ex As Exception
    MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
    Finally
    con.Close()
    End Try
    Hope this answers your question. If you have any concern, please feel free to let me know.

    Thanks
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Vache Thursday, June 25, 2009 5:11 PM
    Thursday, June 25, 2009 3:44 AM
  • Hi,

    You can use Linq to SQL to do that you can create for your self the following sample

    Create a new WindowsForm Project

    Go to the Solution Explorer and in that to the project, click right on the projectg and do New Item

    Select Linq to SQL Classes and click Add.

    A drag surface will become visible.

    Go to Server Explorer and select the table you want to use, I took Northwind table customers and dragged that on the surface
    Now do Build

    Go then to your Form and double click on it, the load event will be opened.
    Normally you don't do this kind of code in that method, but to show

    Dim dc As New DataClasses1DataContext
    Dim theCustomer = (From Customers In dc.Customers Select Customers Where Customers.CustomerID = "ALFKI").First
    'Maria Anders to set it later back
    theCustomer.ContactName = "WhatEver"
    dc.SubmitChanges()
    

    Ready



    Success
    Cor
    Thursday, June 25, 2009 5:18 AM
  • Oh so thank you YiChun Chen! Your code helped me so much. That was an advantage!!! And u know, i`ve got some other difficulties here! How can i make the VALUES (Value1,Value2) to be the data i enter e.g. in a textbox. I just realy want to insert data into SQL table using a textbox, `cause the data should enter the application users. cmd.CommandText = "INSERT INTO table([field1], [field2]) VALUES([Value1], [Value2])" Regards, Vache
    Thursday, June 25, 2009 5:22 PM
  • Hi Vache,

    You can try the following the code:

    "INSERT INTO tableName (field1) VALUES ('" & textBox1.text & "')"
    or

            Dim firstname, lastname As String
    
            firstname = Text1.Text
            lastname = Text2.Text
      
            cmd.CommandText = "INSERT INTO table([field1], [field2]) VALUES(@firstname, @lastname,)"
    

    Hope this helps.

    Thanks

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Edited by YiChun Chen Friday, June 26, 2009 1:51 AM typo
    • Marked as answer by Vache Tuesday, July 14, 2009 5:42 PM
    Friday, June 26, 2009 1:50 AM
  • Thanks so much!!!! This Realy works!!!!!! Regards Vache
    Friday, June 26, 2009 5:04 PM
  • Hi Vache,

    I'm so glad to hear that. Cheer! :)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, June 29, 2009 2:04 AM
  • Thanks buddy
    Saturday, November 14, 2009 4:52 AM
  • This is quite a nifty piece of code YiChun Chen - very helpful especially for us DotNet beginners.  Thanks  
    Monday, November 16, 2009 9:22 PM
  • Hi Chikoz,

    You're welcome. :)

    I'm so glad that it can be helpful.

    Have a great week!

    Best regards,
    Yichun Chen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 17, 2009 3:51 AM
  • Hi,

    Your code is really helpful.....

    Thanks

    Thursday, December 16, 2010 5:01 PM
  • best code for beginners ever

    Friday, April 29, 2011 10:55 AM
  • Hello friends

    Happy new year

    Am young in VB6.0, assist me on how I can insert data to sql server 2005 from form interface. since am able retrieve data from db using wizard.

    Thanks

     

    James

    Wednesday, January 04, 2012 9:02 AM
  • Thank you very much for your explaining. Please you live with happiness
    Thursday, June 14, 2012 10:22 AM
  • Ok i am waiting but i want to detail step define

    thanks,

    Friday, November 09, 2012 7:47 AM
  • @ Syvla

    How to drag a table on to form?

    Tuesday, February 12, 2013 3:55 PM
  • Hi Cor Ligthert,

    When i drag the table onto design surface it is showing error. "The error is the selected object shows an unsupported data provider". What shall i do now..Please guide me


    Regards Syed

    Monday, June 10, 2013 6:49 AM
  • Hi Sylva,


    As you said i dragged the table onto the form, and i am able to feed the data in it. But if i feed any data in forms it is not getting reflected on table. Since i am a beginner i am struck up in this. Help me out.


    Regards Syed

    Tuesday, June 11, 2013 11:34 AM
  • Hi i too have the same above issue as vache said. As you said i dragged onto the form and now i am able to see the tables. So my issue now is if i insert feilds in vb i am not getting that in tables. What do i do.

    Regards Syed

    Thursday, June 13, 2013 7:31 AM
  • Hi vache i am also having the same issue. So what made you to solve this. And other question is are you using sql server express edition which is free edition or any other edition which is paid service.

    Regards Syed

    Thursday, June 13, 2013 7:33 AM
  • Can you please send the complete code, I am new to VB and I really need this code complete.

    Thanks

    Shabir A

    Monday, September 23, 2013 9:33 AM
  • I got this error when I tried your code in a asp.net vb code behind:

    Error while inserting record on table...Invalid column name 'FirstName'. Invalid
    column name 'LastName'.Insert Records

    I set the db table up as Firstname lastName varchar 50

    But this seems to work:

    "INSERT INTO tableName (field1) VALUES ('" & textBox1.text & "')"

    Frustrating !

    If any one can explain pls :) :)


    paul



    Sunday, September 29, 2013 5:17 PM