Updating a Database from a Text Box

[pkw25]

Hello

I am having a problem where i cant update a database from a text Box. It works if the textbox I am up updating from textbox3 is not a bound control. But I need to bind it because I want the control to display the current data.
Is there a way of displaying without binding ?
Any help would be much appreciated.

regards
Paraic

Public Sub Form1_Load..................

TextBox1.DataBindings.Add("Text", ds, "tblSPECIALH.ID")
TextBox2.DataBindings.Add("Text", ds, "tblSPECIALH.ENTERED DATE")
TextBox3.DataBindings.Add("Text", ds, "tblSPECIALH.EVENT")


TextBox4.DataBindings.Add("Text", ds, "tblSPECIALH.PARTICIPANT1")
TextBox5.DataBindings.Add("Text", ds, "tblSPECIALH.EVENT")

End Sub



Private Sub UpdatetblSPECIALH(ByVal sqldataadapater4 As SqlDataAdapter)


Dim IDV As Integer = 0
Dim Eve As String = ""

Eve = TextBox3.Text
IDV = TextBox1.Text

Dim Sql As String = String.Format("UPDATE tblSPECIALH SET EVENT = '{0}' WHERE ID = '{1}'", Eve, IDV)
Dim Command2 As SqlCommand = New SqlCommand(Sql, Connection1)

sqldataadapter4.UpdateCommand = Command2

Dim SpeRowToUpdate As DataRow() = ds.Tables("tblSPECIALH").Select(String.Format("ID = '{0}'", IDV))
SpeRowToUpdate(0)("Event") = Eve

Try
sqldataadapter4.Update(ds.Tables("tblSPECIALH"))
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try

End Sub
End Class

 

[Qik3Coder]

pkw25, you can do your commands without using the data adapters, which may be a little cleaner.

Dim cmd As SqlClient.SqlCommand
'build update command
Dim intSQLStatus As Integer
Try
   cmd.Connection.Open()
   intSQLStatus = cmd.ExecuteNonQuery()
   Return True
Catch e As Exception
End Try

-The answer to your problem may not be the answer to your question.

 

[pkw25]

Qik3Coder,
Thanks alot,that worked. this one really baffled me for a long time. I still don't understand why it worked or why the dataadapter didn't.
Here is the code.



Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Connection1.Open()

Dim Command1 As SqlCommand = New SqlCommand("SELECT * FROM tblSPECIALH", Connection1)

myDataReader = Command1.ExecuteReader()
myDataReader.Read()

TextBox1.Text = myDataReader("ID").ToString
TextBox2.Text = myDataReader("ENTERED DATE").ToString
TextBox3.Text = myDataReader("Event").ToString

End Sub


Private Sub UpdatetblSPECIALH(ByVal sqldataadapater4 As SqlDataAdapter)



Dim IDV As Integer = 0
Dim Eve As String = ""

Eve = TextBox3.Text
IDV = TextBox1.Text


Dim Sql As String = String.Format("UPDATE tblSPECIALH SET EVENT = '{0}' WHERE ID = '{1}'", Eve, IDV)
Dim Command2 As SqlCommand = New SqlCommand(Sql, Connection1)

myDataReader.Close()
Command2.ExecuteNonQuery()


End Sub

 

[Qik3Coder]

This bypasses a lot of the extra objects that deal with data access and says "i know exactly what i want". I usually use it for smaller data returns.

If I have a large dataset that I need to display I use a generic data adapter helper that I have.

-The answer to your problem may not be the answer to your question.

 

[pkw25]

I am new to vb2005 but from what I've read DataAdapters and Datasets look to be the better option becuase of the Disconnected layer etc. I intend to use bigger queries but also text boxes and datagrids. I have searched all over but I can't see a way of populating a textbox and then updating a database from that same textbox using an adapter and a dataset.
From what you have said I think I am better of knowing both methods