How to INSERT multiple rows of data to sql table?

zaimah

Hi, anyone can give me idea on how to do this? I'm used to gridview for updating multiple data to sql table. But how can i insert to sql table a list of data from a table/form? Its a situation where a lecturer wants to register a list of student for a subject.

Lost User

One way would be to issue INSERT commands, using Sql. Another way would be using the TableAdapters. Both approaches are described here[^].

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

zaimah

sorry.. still cant understand how it works. i have a html table where there are 3 columns studentID,studentName, courseName. There ada 10 rows of textbox for each column. Can u explain how can i use the one that u gave me? I'm not using gridview because my data doesn't exist in the sql table yet. Hope my explanation is clear because my english is not that good.. TQ for the reply... been waiting every minutes for reply :)

Lost User

zaimah wrote:

sorry.. still cant understand how it works.
i have a html table where there are 3 columns studentID,studentName, courseName. There ada 10 rows of textbox for each column. Can u explain how can i use the one that u gave me?

That explanation would not fit in a single post.

zaimah wrote:

I'm not using gridview because my data doesn't exist in the sql table yet.

You'd need to CREATE TABLE before the INSERT. Both are done using Sql, a separate language with it's own syntax. The other method that's being described in the documentation, is the TableAdapter-approach, where the framework generates these commands on your behalf.

zaimah wrote:

been waiting every minutes for reply :)

As opposed to waiting, find yourself a tutorial, an article, anything that gets you moving in the right direction. It might take a few days before you get a satisfactory answer.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

zaimah

sorry, maybe my explanation is not clear. I already have a table "student". And i have a web form where the lecturer can insert a list of new student. I cannot use gridview not because the table doesn't exist, just the data does not exist yet until the lecturer key-in the data. i know how to use sqldataAdapter to retrieve value from sql table and put it in a textbox. But that is if the data exist. Will try to look for tableadapter. Sorry again, i waited and at the same time been searching for solution all over the place for any articles related to my problem. TQ for your reply..

Lost User

zaimah wrote:

I already have a table "student". And i have a web form where the lecturer can insert a list of new student. I cannot use gridview not because the table doesn't exist, just the data does not exist yet until the lecturer key-in the data.

You'd also need a list that contains the students that are linked to a course, and execute insert-commands to fill that. It also means that the student will have to exist, before this list can be saved.

zaimah wrote:

i know how to use sqldataAdapter to retrieve value from sql table and put it in a textbox. But that is if the data exist.

That's an update. You'll need something similar for an insert.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

zaimah

Its a bit confusing rite now for me. The lecturer job is to register the student to the table "student". What do u mean by the student will have to exist before the list can be save? Do u mean i have to type manually to the table "student"? Been trying to find Insert from html table to sql table but keep on seeing from sql table to html table... Can tableadapter used for webform?

Lost User

zaimah wrote:

The lecturer job is to register the student to the table "student". What do u mean by the student will have to exist before the list can be save? Do u mean i have to type manually to the table "student"?

The list you are trying to save is a combination of the student, and some other record. Before you can save anything that's linked to a student-record, the student-record must exist in the database. So, first the student is "inserted", then the rest.

zaimah wrote:

Been trying to find Insert from html table to sql table but keep on seeing from sql table to html table...

Data is seldom saved in the same way as it is presented. Can you list the other tables that you have besides the student-table?

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

zaimah

that is what im trying to do.. Insert the student.. How can i do that? Insert a list of student? after that, it would be easier for me to use UPDATE because the student exist in the student table. I have table for student so i can set their course in table course.

Lost User

You don't insert a list, but a record; each record is an item in the list. You'd need to execute an insert-command (yup, still), something similar to this;

Public Sub DoInsert()
Using connection As New SqlConnection(connectionString) ' look up yours on www.connectionstrings.com
Dim command As SqlCommand = connection.CreateCommand()
command.Connection.Open() ' open the database
' command below needs a fitting insert-command, based on your tablestructure
command.CommandText = "INSERT INTO [studenttablename] (StudName, StudAge) VALUES @StudName, @StudAge)"

    command.Parameters.AddWithValue("@StudName", "Goku") ' put the value of the textbox here
    command.Parameters.AddWithValue("@StudAge", "9000+")

    command.ExecuteNonQuery()
End Using

End Sub

Do that for each item, and the list is "inserted" in the table.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

zaimah

is there a way for me to insert all using this coding? lets say i have 10 record, how can i use this code to insert each record that i key-in? i want to insert 10 or more records at a time. I manage to UPDATE multiple record using one button on grid view. But for INSERT, i can only manage to INSERT record one by one. Is there a way for me to INSERT lets say 10 records at a time?

Lost User

zaimah wrote:

is there a way for me to insert all using this coding?

Yes, by executing that code ten times. If it needs to be a single action, it'd be wrapped in a transaction. Uploading a list is usually done when importing large text-files - it's called a batch insert.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

Andy_L_J

Look up Table Value Parameters. This may help you understand Table-Value Parameters in SQL Server 2008 - VB.NET[^]

I don't speak Idiot - please talk slowly and clearly "I have sexdaily. I mean dyslexia. Fcuk!" Driven to the arms of Heineken by the wife

April Fans

Hi, I am recently using SQL for a project of my own. I came across this post and thought I might be able to offer some input. For example, in my team group is creating a database. There is a way to insert multiple rows into a table in SQL without having to insert them individually. You could formulate the data to be inserted as select statements for multiple list row entries, but I believe for this to be accomplished you may have to do two stages: 1)Put the data into a temporary table 2)Insert from that table. Live Support Software for Business

April Comm100 - Leading Live Chat Software Provider