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!

Answer 1

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!

Answer 2

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)

Answer 3

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

Answer 4

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...

Answer 5

E.g. i want to insert the data that is entered in a textbox or is included in combobox list. Thanks for care guys!

Answer 6

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!

Answer 7

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.

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

Thanks so much!!!! This Realy works!!!!!! Regards Vache

Answer 12

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.

Answer 13

Thanks buddy

Answer 14

This is quite a nifty piece of code YiChun Chen - very helpful especially for us DotNet beginners.  Thanks  

Answer 15

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.

Answer 16

Hi,

Your code is really helpful.....

Thanks

Answer 17

best code for beginners ever

Answer 18

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

Answer 19

Thank you very much for your explaining. Please you live with happiness

Answer 20

Ok i am waiting but i want to detail step define

thanks,

Answer 21

@ Syvla

How to drag a table on to form?