Multiple Inserts fail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Multiple Inserts fail

Author

Topic

JimmyFo
Starting Member

11 Posts

Posted - 2005-07-15 : 11:47:15

Hi all, I have a page where a user can click to add a new item to a list. The item is submitted to a SQL database. Submitting works, but for some reason, only one item exists in the database at a time.In other words, when I submit an item, it goes into the database, but only into the first row - anything there previously is deleted/overwritten. I can check the database and it is being submitted properly.


Public Sub NewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewUserBtn.ServerClick
        Dim MyConnection As SqlConnection = New SqlConnection("server=server;database=db;uid=uid;pwd=pwd")
        Dim MyCommand As SqlCommand = New SqlCommand("insert into certifications (name, empID) values (@name, @empID)", MyConnection)

        MyCommand.Parameters.Add("@name", newFName.Text & " " & newLName.Text)
        MyCommand.Parameters.Add("@empID", newEmpID.Text)

        Dim DA As SqlDataAdapter = New SqlDataAdapter
        DA.SelectCommand = MyCommand

        MyConnection.Open()

        Dim DS As DataSet = New DataSet
        DA.Fill(DS, "Titles")

        MyConnection.Close()
        Response.Redirect("WebForm1.aspx", True)
    End Sub

Any ideas as to why this happens?
Also, it was suggested to me to use executenonquery() for single inserts like this - is that a good idea? Thanks.

James

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-15 : 12:02:42

>>Also, it was suggested to me to use executenonquery() for single inserts like this - is that a good idea? Thanks.

Ummm .... yes. Why don't you read up on ADO.NET and the ExecuteNonQuery() method and the command object in general? You have to *do something* to actually execute the stored proc; at no point in your code are you ever doing that. Worse, you are misusing a data adapter when you don't need one here, and you are setting the "SELECT COMMAND" property of the data adapter to a SQL insert statement.

To execute a simple command in SQL, you create the command, set parameters, and then execute it. At no point is a data apapter needed; data adapters are used to keep local .NET dataTables and sql server tables in synch.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources