Updating SQL server database from a dataset

[Jnox]

Any idea why this code doesnt work? no errors

SqlDataAdapter1.Update(DataSet11.GetChanges, "Users")

                    myConnection = New SqlConnection("uid=xxxxx;pwd=xxxxx;data source=LAPTOP\SQLEXPRESS;initial catalog=CheckWeigher")


                    'myCommand = New System.Data.SqlClient.SqlCommand("DELETE FROM Users WHERE (UserID, FirstName, LastName, Title, Password) = (@UserID, @FirstName, @LastName, @Title, @Password)", myConnection)
                    myCommand = New SqlCommand("DELETE FROM Users WHERE UserID = @UserID", myConnection)
                    myCommand.Parameters.Add("@UserID", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@UserID").Value = TextBox2.Text

                    myCommand.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@FirstName").Value = "bb" 'TextBox3.Text

                    myCommand.Parameters.Add("@LastName", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@LastName").Value = "cc" 'TextBox4.Text

                    myCommand.Parameters.Add("@Title", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@Title").Value = "dd" 'TextBox10.Text

                    myCommand.Parameters.Add("@Password", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@Password").Value = "ee" 'TextBox5.Text

                    myConnection.Open()
                    myCommand.ExecuteNonQuery()
                    myConnection.Close()

                Catch ex As Exception
                End Try

 

[vis781]

Have you got the SQL generated by the wizard or are you writing it yourself? If it's the latter then it will be helpful to see your update query

 

[Jnox]

Have you got the SQL generated by the wizard or are you writing it yourself? If it's the latter then it will be helpful to see your update query

I am very new at this. Here is my dataset code:

<?xml version="1.0" standalone="yes" ?>
<xs:schema id="DataSet1" targetNamespace="http://www.tempuri.org/DataSet1.xsd" xmlns:mstns="http://www.tempuri.org/DataSet1.xsd"
    xmlns="http://www.tempuri.org/DataSet1.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
    <xs:element name="DataSet1" msdata:IsDataSet="true" msdata:Locale="en-US">
        <xs:complexType>
            <xs:choice maxOccurs="unbounded">
                <xs:element name="Users">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="UserID" type="xs:string" />
                            <xs:element name="FirstName" type="xs:string" minOccurs="0" />
                            <xs:element name="LastName" type="xs:string" minOccurs="0" />
                            <xs:element name="Title" type="xs:string" minOccurs="0" />
                            <xs:element name="Password" type="xs:string" minOccurs="0" />
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:choice>
        </xs:complexType>
        <xs:unique name="Constraint1" msdata:PrimaryKey="true">
            <xs:selector xpath=".//mstns:Users" />
            <xs:field xpath="mstns:UserID" />
        </xs:unique>
    </xs:element>
</xs:schema>

 

[cjard]

your command text:

myCommand = New SqlCommand("DELETE FROM Users WHERE UserID = @UserID", myConnection)

contains only one parameter. You do not need to add all 5 parameters:

                    myCommand.Parameters.Add("@UserID", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@UserID").Value = TextBox2.Text
 
                    myCommand.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@FirstName").Value = "bb" 'TextBox3.Text
 
                    myCommand.Parameters.Add("@LastName", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@LastName").Value = "cc" 'TextBox4.Text
 
                    myCommand.Parameters.Add("@Title", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@Title").Value = "dd" 'TextBox10.Text
 
                    myCommand.Parameters.Add("@Password", Data.SqlDbType.NVarChar)
                    myCommand.Parameters.Item("@Password").Value = "ee" 'TextBox5.Text

never, ever, ever do this when youre learning:

                Catch ex As Exception
                End Try

if you catch the exception and hide it (do nothing with it) then you will NEVER know if an error has occurred (unless you configured the IDE to break on throw rather than break on unhandled, but thats beyond the scope of this post)

Just dont do it; youre tormenting yourself if you do.
At the very least, put

                Catch ex As Exception
                   MessageBox.Show(ex.StackTrace)
                End Try

as a final point, if I wrote an oracle query (because I use oracle, not SQL Server) like this:

myCommand = New SqlCommand("DELETE FROM Users WHERE UserID = :UserID", myConnection)

I would write this for the parameter:

myCommand.Parameters.Add("UserID", OracleDbType.VarChar).Value = "some user id"

the differences?
1) I wrote :UserID in the command text. That colon : means "a parameter" and the text following is the name.. so the name of the parameter (for oracle code) is UserID, not :UserID
This is why i add a parameter called "UserID", not ":UserID"
It may be the case that VB automatically detects this and takes it out, i dont know. If your commands are failing you can try them without the @ sign in the NAME of the parameter
2) the .Add() method returns a reference to the parameter it just created. Hence you can call .Value on the return value of .Add() because .Add() returns a Parameter object.. THis negates the need to write the next line of myCommand.Parameters.Item("@Title").Value ...