Update SQL DB From Textboxes???

[Neumee]

***************Overview of Scenerio********************
1. I load records into a datagrid
2. Then I dbl-click a record and then the record is loaded into the textboxes on another form.
3. Not all of the textboxes actually has data...IE- A record may not have a price or something along those lines
4. So I want to be able to make any changes I want, or add the price or something and then click Modify Changes Button, and then it saves the changes back to the Database.
5. But if the textbox is empty, then it should just ignore it or write Nothing back to the DB or however that works.

Any Help is greatly appreciated. Thanks in Advance

Here is some sample code
****************************************************
Private Sub Form4_Load

mSql = "SELECT PAR_DISC , PAR_NUMBER , PAR_P_CODE , PAR_COST , PAR_LIST , PAR_LOC , PAR_DEPT , PAR_STCK , PAR_SOLD , PAR_PKG , PAR_XREF , PAR_WIN_MI , PAR_SUM_MI FROM Polaris1 WHERE(PAR_NUMBER LIKE N'" & TextBox1.Text & "%') "

mDa = New SqlClient.SqlDataAdapter(mSql, PolarisConnection)
mDa.Fill(mModifyTable)

Dim b1 As Binding = TextBox1.DataBindings.Add("Text", mModifyTable, "PAR_NUMBER")

Dim b2 As Binding = TextBox2.DataBindings.Add("Text", mModifyTable, "PAR_DISC")

Dim b3 As Binding = TextBox3.DataBindings.Add("Text", mModifyTable, "PAR_XREF")

Dim b4 As Binding = TextBox4.DataBindings.Add("Text", mModifyTable, "PAR_P_CODE")

Dim b5 As Binding = TextBox5.DataBindings.Add("Text", mModifyTable, "PAR_LIST")

Dim b6 As Binding = TextBox6.DataBindings.Add("Text", mModifyTable, "PAR_COST")

Dim b7 As Binding = TextBox7.DataBindings.Add("Text", mModifyTable, "PAR_SUM_MI")

Dim b8 As Binding = TextBox8.DataBindings.Add("Text", mModifyTable, "PAR_WIN_MI")

Dim b9 As Binding = TextBox9.DataBindings.Add("Text", mModifyTable, "PAR_DEPT")

Dim b10 As Binding = TextBox10.DataBindings.Add("Text", mModifyTable, "PAR_LOC")

Dim b11 As Binding = TextBox11.DataBindings.Add("Text", mModifyTable, "PAR_STCK")

Dim b12 As Binding = TextBox12.DataBindings.Add("Text", mModifyTable, "PAR_SOLD")

Dim b13 As Binding = TextBox13.DataBindings.Add("Text", mModifyTable, "PAR_PKG")

TextBox1.DataBindings.Remove(b1)
TextBox2.DataBindings.Remove(b2)
TextBox3.DataBindings.Remove(b3)
TextBox4.DataBindings.Remove(b4)
TextBox5.DataBindings.Remove(b5)
TextBox6.DataBindings.Remove(b6)
TextBox7.DataBindings.Remove(b7)
TextBox8.DataBindings.Remove(b8)
TextBox9.DataBindings.Remove(b9)
TextBox10.DataBindings.Remove(b10)
TextBox11.DataBindings.Remove(b11)
TextBox12.DataBindings.Remove(b12)
TextBox13.DataBindings.Remove(b13)

TextBox5.Text = Format(TextBox5.Text, "Currency")
TextBox6.Text = Format(TextBox6.Text, "Currency")
****************************************************
So the above code loads the data into the textboxes, which works fine.

****************************************************
This Is the Modify Changes Button

Private Sub Button1_Click

This commandstring could be all my problems. I dont know!

Dim commandstring As String = "UPDATE Polaris1 SET PAR_DISC = @PAR_DISC, PAR_NUMBER = @PAR_NUMBER, PAR_P_CODE = @PAR_P_CODE, PAR_COST = @PAR_COST, PAR_LIST = @PAR_LIST, PAR_LOC = @PAR_LOC, PAR_DEPT = @PAR_DEPT, PAR_STCK = @PAR_STCK, PAR_SOLD = @PAR_SOLD, PAR_PKG = @PAR_PKG, PAR_XREF = @PAR_XREF, PAR_WIN_MI = @PAR_WIN_MI, PAR_SUM_MI = @PAR_SUM_MI WHERE (PAR_NUMBER = @PAR_NUMBER) AND (PAR_COST = @PAR_COST OR @PAR_COST IS NULL AND PAR_COST IS NULL) AND (PAR_DEPT = @PAR_DEPT OR @PAR_DEPT IS NULL AND PAR_DEPT IS NULL) AND (PAR_DISC = @PAR_DISC OR @PAR_DISC IS NULL AND PAR_DISC IS NULL) AND (PAR_LIST = @PAR_LIST OR @PAR_LIST IS NULL AND PAR_LIST IS NULL) AND (PAR_LOC = @PAR_LOC OR @PAR_LOC IS NULL AND PAR_LOC IS NULL) AND (PAR_PKG = @PAR_PKG OR @PAR_PKG IS NULL AND PAR_PKG IS NULL) AND (PAR_P_CODE = @PAR_P_CODE OR @PAR_P_CODE IS NULL AND PAR_P_CODE IS NULL) AND (PAR_SOLD = @PAR_SOLD OR @PAR_SOLD IS NULL AND PAR_SOLD IS NULL) AND (PAR_STCK = @PAR_STCK OR @PAR_STCK IS NULL AND PAR_STCK IS NULL) AND (PAR_SUM_MI = @PAR_SUM_MI OR @PAR_SUM_MI IS NULL AND PAR_SUM_MI IS NULL) AND (PAR_WIN_MI = @PAR_WIN_MI OR @PAR_WIN_MI IS NULL AND PAR_WIN_MI IS NULL) AND (PAR_XREF = @PAR_XREF OR @PAR_XREF IS NULL AND PAR_XREF IS NULL); SELECT PAR_DISC, PAR_NUMBER, PAR_P_CODE, PAR_COST, PAR_LIST, PAR_LOC, PAR_DEPT, PAR_STCK, PAR_SOLD, PAR_PKG, PAR_XREF, PAR_WIN_MI, PAR_SUM_MI FROM Polaris1 WHERE (PAR_NUMBER = @PAR_NUMBER)"

Dim sqlcommand As New SqlCommand(commandstring, PolarisConnection)

sqlcommand.Parameters.Add(New SqlParameter("@PAR_DISC", SqlDbType.NVarChar, 50))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_NUMBER", SqlDbType.NVarChar, 50))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_P_CODE", SqlDbType.NVarChar, 1))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_COST", SqlDbType.Float, 8))
sqlcommand.Parameters.Add(New SqlParameter("@PAR_LIST", SqlDbType.Float, 8))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_LOC", SqlDbType.NVarChar, 5))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_DEPT", SqlDbType.NVarChar, 2))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_STCK", SqlDbType.Float, 8))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_SOLD", SqlDbType.Float, 8))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_PKG", SqlDbType.Float, 8))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_XREF", SqlDbType.NVarChar, 30))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_WIN_MI", SqlDbType.Float, 8))

sqlcommand.Parameters.Add(New SqlParameter("@PAR_SUM_MI", SqlDbType.Float, 8))

sqlcommand.Connection.Open()
sqlcommand.Parameters("@PAR_NUMBER").Value = (TextBox1.Text)

sqlcommand.Parameters("@PAR_DISC").Value = (TextBox2.Text)

sqlcommand.Parameters("@PAR_P_CODE").Value = (TextBox4.Text)

sqlcommand.Parameters("@PAR_COST").Value = (TextBox6.Text)

sqlcommand.Parameters("@PAR_LIST").Value = (TextBox5.Text)

sqlcommand.Parameters("@PAR_LOC").Value = (TextBox10.Text)

sqlcommand.Parameters("@PAR_DEPT").Value = (TextBox9.Text)

sqlcommand.Parameters("@PAR_STCK").Value = (TextBox11.Text)

sqlcommand.Parameters("@PAR_SOLD").Value = (TextBox12.Text)

sqlcommand.Parameters("@PAR_PKG").Value = (TextBox13.Text)

sqlcommand.Parameters("@PAR_XREF").Value = (TextBox3.Text)

sqlcommand.Parameters("@PAR_WIN_MI").Value = (TextBox8.Text)

sqlcommand.Parameters("@PAR_SUM_MI").Value = (TextBox7.Text)


Try
sqlcommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
****************************************************

[Athley]

In this case I would use a dataset and databinding. If you bind every control to the table of the dataset at then create aBindingManagerBase to control the binding I dont think you even have to set the position actually, if I remember correctly. But if you happen to be on the wrong position just set the BindingManagerBase.Position property to that one of wich you clicked. If you do this the only thing to do when updating the data is to use the DataAdapter.Update method after you have called the BindingManagerBase.EndCurrentEdit method.

[Neumee]

The thing is, I dont load all of my Database fields into the datagrid itself. When I dbl-click the datagrid, it reads the part number from the datagrid and then it query's the DB for the rest of the fields in the DB. I wasn't aware of how to do the method you are describing, so maybe it will still work?? Can you maybe give me an example of how to do this? Thanks for the help.

[Athley]

Sure, something like this to fill the dataview and datagrid:

Dim cn As New OleDb.OleDbConnection("Connectionstring......")
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Table1", cn)
Dim ds As New DataSet()
Dim dv As DataView
da.Fill(ds)
dv = ds.Tables("Table1").DefaultView
dv.RowFilter = "FirstName = Steve"
datagrid1.DataSource = dv

and then call the new form and send the ID (you have to modify the constructor of the second form to take the ID as an argument)

Dim frm As New Form2(dv.item(datagrid1.CurrentRowIndex)("ID"))
frm.Show()

Didn't try this in code just typed it here so bare with me if you find a type error.