Updating/Writing to SQL Server
-
I have a form that has multiple textboxes that a user needs to input data into in order to write to several tables within sql server. All of the controls on the form are data binded to a column within a specific table. I can read from all tables using the (generic) code command: DataAdapter.Fill(DataSet) My problem is that I can not update/write to the database. I'm using the (generic) code command: DataAdapter.Update(DataSet), so that when any change is made to any textbox control when the user triggers the click event button to update the database it will update/write to the database using what is currently in the textboxes. I receive no error messages and my 'CommandText' property under the dataAdapter has the sql code generated to insert a into the proper fields and tables. Any idea(s) why I can not update/write to the sql server? Language: vb.net 2003 Server: sql server 2000 Shannon P.
-
I have a form that has multiple textboxes that a user needs to input data into in order to write to several tables within sql server. All of the controls on the form are data binded to a column within a specific table. I can read from all tables using the (generic) code command: DataAdapter.Fill(DataSet) My problem is that I can not update/write to the database. I'm using the (generic) code command: DataAdapter.Update(DataSet), so that when any change is made to any textbox control when the user triggers the click event button to update the database it will update/write to the database using what is currently in the textboxes. I receive no error messages and my 'CommandText' property under the dataAdapter has the sql code generated to insert a into the proper fields and tables. Any idea(s) why I can not update/write to the sql server? Language: vb.net 2003 Server: sql server 2000 Shannon P.
My advice is to write one stored procedure that updates all the appropriate tables. Once you have your stored procedure written call it like this. Also put imports data.sqlclient at the top of your page. This is an example from the project I'm working on, obviously you need to use your own names / variables. Private Function UpdateLiabilityRecord(ByVal intClassID As Integer) Dim Conn As New SqlConnection Conn.ConnectionString = Session("ConnectionString") Dim Cmd As New SqlCommand Cmd.Connection = Conn Cmd.CommandType = CommandType.StoredProcedure Cmd.CommandText = "dbo.USP_AM_UpdateLiabilityClass" Dim PrmClassID As New SqlParameter("@ClassID", SqlDbType.Int) PrmClassID.Direction = ParameterDirection.Input Cmd.Parameters.Add(PrmClassID) PrmClassID.Value = intClassID Dim PrmClassCode As New SqlParameter("@ClassCode", SqlDbType.Char, 6) PrmClassCode.Direction = ParameterDirection.Input Cmd.Parameters.Add(PrmClassCode) PrmClassCode.Value = Right("000000" & Trim(Me.txtClassCode.Text), 6) Dim PrmEffectiveDate As New SqlParameter("@EffectiveDate", SqlDbType.DateTime) PrmEffectiveDate.Direction = ParameterDirection.Input Cmd.Parameters.Add(PrmEffectiveDate) PrmEffectiveDate.Value = CDate(Me.txtEffDate.Text) Conn.Open() Cmd.ExecuteNonQuery() Conn.Close() End Function Of course, don't forget to validate your data first. "People who never make mistakes, never do anything." My blog http://toddsnotsoamazinglife.blogspot.com/