Persistant DataSet data entry problem
-
Hi Everyone: This problem has been plaguing me for three days, and I can’t for the life of me find a logical reason why this problem occurs. My problem occurs when I try to write data into a dataset that contains nothing more than the table schema. This subroutine is supposed to write a new record into the dataset. Private Sub New_Bindings(ByVal intOrdinal As Integer, _ ByVal strCode As String, _ ByVal strName As String) tblBindings = MyDataSet.Tables("tblBindings") drCurrent = tblBindings.NewRow() drCurrent("Ordinal") = intOrdinal drCurrent("BindingCode") = strCode drCurrent("BindingName") = strName tblBindings.Rows.Add(drCurrent) End Sub I write new data into the dataset when this subroutine is activated. It is activated when the Private Sub BindingNavigatorAddNewItem_Click event is triggered: The AddNew Button on the BindingNavigator control on the form. Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click Call New_Bindings(1, "SDLS", "Saddle-stitch") Call New_Bindings(2, "SPRL", "Spiral Bound") Call New_Bindings(3, "STUL", "Staple Upper Left") Call New_Bindings(4, "STUR", "Staple Upper Right") Call New_Bindings(5, "ST2L", "Two Staples on left side") End Sub The dataset’s data should read like this: BindingID – 1 Ordinal – 1 Code – SDLS Name – Saddle-stitch BindingID – 2 Ordinal – 2 Code – SPRL Name – Spiral Bound BindingID – 3 Ordinal – 3 Code – STUL Name – Staple Upper Left BindingID – 4 Ordinal – 4 Code – STUP Name – Staple Upper Right BindingID – 5 Ordinal – 5 Code – ST2l Name – Two Staples on Left Side What I am actually getting is: BindingID – 0 Ordinal – 1 Code – SDLS Name – Saddle-stitch BindingID – 1 Ordinal – 2 Code – SPRL Name – Spiral Bound BindingID – 2 Ordinal – 3 Code – STUL Name – Staple Upper Left BindingID – 3 Ordinal – 4 Code – STUP Name – Staple Upper Right BindingID – 4 Ordinal – 5 Code – ST2l Name – Two Staples on Left Side For some reason when the first record is written out to the dataset the auto increment field (BindingID) is not incremented to one when the record is written. It has an ID of “0” which logically can’t happen when the column has been defined to be an auto increment column starting at one and inc
-
Hi Everyone: This problem has been plaguing me for three days, and I can’t for the life of me find a logical reason why this problem occurs. My problem occurs when I try to write data into a dataset that contains nothing more than the table schema. This subroutine is supposed to write a new record into the dataset. Private Sub New_Bindings(ByVal intOrdinal As Integer, _ ByVal strCode As String, _ ByVal strName As String) tblBindings = MyDataSet.Tables("tblBindings") drCurrent = tblBindings.NewRow() drCurrent("Ordinal") = intOrdinal drCurrent("BindingCode") = strCode drCurrent("BindingName") = strName tblBindings.Rows.Add(drCurrent) End Sub I write new data into the dataset when this subroutine is activated. It is activated when the Private Sub BindingNavigatorAddNewItem_Click event is triggered: The AddNew Button on the BindingNavigator control on the form. Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click Call New_Bindings(1, "SDLS", "Saddle-stitch") Call New_Bindings(2, "SPRL", "Spiral Bound") Call New_Bindings(3, "STUL", "Staple Upper Left") Call New_Bindings(4, "STUR", "Staple Upper Right") Call New_Bindings(5, "ST2L", "Two Staples on left side") End Sub The dataset’s data should read like this: BindingID – 1 Ordinal – 1 Code – SDLS Name – Saddle-stitch BindingID – 2 Ordinal – 2 Code – SPRL Name – Spiral Bound BindingID – 3 Ordinal – 3 Code – STUL Name – Staple Upper Left BindingID – 4 Ordinal – 4 Code – STUP Name – Staple Upper Right BindingID – 5 Ordinal – 5 Code – ST2l Name – Two Staples on Left Side What I am actually getting is: BindingID – 0 Ordinal – 1 Code – SDLS Name – Saddle-stitch BindingID – 1 Ordinal – 2 Code – SPRL Name – Spiral Bound BindingID – 2 Ordinal – 3 Code – STUL Name – Staple Upper Left BindingID – 3 Ordinal – 4 Code – STUP Name – Staple Upper Right BindingID – 4 Ordinal – 5 Code – ST2l Name – Two Staples on Left Side For some reason when the first record is written out to the dataset the auto increment field (BindingID) is not incremented to one when the record is written. It has an ID of “0” which logically can’t happen when the column has been defined to be an auto increment column starting at one and inc
Quecumber256 wrote:
tblBindings = MyDataSet.Tables("tblBindings") drCurrent = tblBindings.NewRow() drCurrent("Ordinal") = intOrdinal drCurrent("BindingCode") = strCode drCurrent("BindingName") = strName tblBindings.Rows.Add(drCurrent)
Wow I just saw something here. You need to do the following instead.
drCurrent = MyDataSet.Tables("tblBindings").NewRow()
drCurrent("Ordinal") = intOrdinal
drCurrent("BindingCode") = strCode
drCurrent("BindingName") = strName
MyDataSet.Tables("tblBindings").Rows.Add(drCurrent)That is one issue. The other I still cant see based on what you provided.
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
-
Quecumber256 wrote:
tblBindings = MyDataSet.Tables("tblBindings") drCurrent = tblBindings.NewRow() drCurrent("Ordinal") = intOrdinal drCurrent("BindingCode") = strCode drCurrent("BindingName") = strName tblBindings.Rows.Add(drCurrent)
Wow I just saw something here. You need to do the following instead.
drCurrent = MyDataSet.Tables("tblBindings").NewRow()
drCurrent("Ordinal") = intOrdinal
drCurrent("BindingCode") = strCode
drCurrent("BindingName") = strName
MyDataSet.Tables("tblBindings").Rows.Add(drCurrent)That is one issue. The other I still cant see based on what you provided.
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
Cleako: Just to let you know I appreciate your help. I tried your fix. I still get the same results. Therefore I'm providing you the entire code for the data entry form. I hope this give you enough information to see what is causing the first record to not be recorded properly. Option Explicit On Imports System.Data.SqlClient Public Class frmBindings Private Msg As String Private strSQL As String 'Declare the SQLDataAdapter and DataSet objects Private daBindings As New SqlDataAdapter() Private MyDataSet As New DataSet() Dim tblBindings As DataTable Dim drCurrent As DataRow Private Sub Clear_Form() txtBindingCode.Text = Nothing txtBindingName.Text = Nothing txtOrdinal.Text = Nothing End Sub Private Sub Load_DataSet() 'Connect to the SQL Server database Dim cn As SqlConnection = New SqlConnection((My.Settings.PrintsByMe_DevConnectionString)) cn.Open() 'Retrieve the data using a SQL statement strSQL = "SELECT * FROM [tblBindings];" Dim cd As New SqlCommand(strSQL, cn) 'Set the database connection for MySqlDataAdapter daBindings.SelectCommand = cd 'Fill the DataSet and DataSet Schema daBindings.FillSchema(MyDataSet, SchemaType.Source, "tblBindings") daBindings.Fill(MyDataSet, "tblBindings") 'Close database connection cn.Close() 'Set the BindingNavigator's DataSource to the DataSet we created. bsBindings.DataSource = MyDataSet 'Set the BindingSource Datamember to the table we are using. bsBindings.DataMember = MyDataSet.Tables(0).TableName() 'Bind form control txtBindingID to the BindingID field txtBindingID.DataBindings.Add("Text", bsBindings, "BindingID") 'Bind form control txtOrdinal to the Ordinal field txtOrdinal.DataBindings.Add("Text", bsBindings, "Ordinal") 'Bind form control txtBindingCode to the BindingCode field txtBindingCode.DataBindings.Add("Text", bsBindings, "BindingCode") 'Bind form control txtBindingName to the BindingName Field txtBindingName.DataBindings.Add("Text", bsBindings, "BindingName") End Sub Private Sub New_Bindings(ByVal intOrdinal As Integer, _ ByVal strCode As String, _ ByVal strName As String) drCurrent = MyDataSet.Tables("tblBindings").NewRow() drCurrent("Ordinal") = intOrdinal
-
Cleako: Just to let you know I appreciate your help. I tried your fix. I still get the same results. Therefore I'm providing you the entire code for the data entry form. I hope this give you enough information to see what is causing the first record to not be recorded properly. Option Explicit On Imports System.Data.SqlClient Public Class frmBindings Private Msg As String Private strSQL As String 'Declare the SQLDataAdapter and DataSet objects Private daBindings As New SqlDataAdapter() Private MyDataSet As New DataSet() Dim tblBindings As DataTable Dim drCurrent As DataRow Private Sub Clear_Form() txtBindingCode.Text = Nothing txtBindingName.Text = Nothing txtOrdinal.Text = Nothing End Sub Private Sub Load_DataSet() 'Connect to the SQL Server database Dim cn As SqlConnection = New SqlConnection((My.Settings.PrintsByMe_DevConnectionString)) cn.Open() 'Retrieve the data using a SQL statement strSQL = "SELECT * FROM [tblBindings];" Dim cd As New SqlCommand(strSQL, cn) 'Set the database connection for MySqlDataAdapter daBindings.SelectCommand = cd 'Fill the DataSet and DataSet Schema daBindings.FillSchema(MyDataSet, SchemaType.Source, "tblBindings") daBindings.Fill(MyDataSet, "tblBindings") 'Close database connection cn.Close() 'Set the BindingNavigator's DataSource to the DataSet we created. bsBindings.DataSource = MyDataSet 'Set the BindingSource Datamember to the table we are using. bsBindings.DataMember = MyDataSet.Tables(0).TableName() 'Bind form control txtBindingID to the BindingID field txtBindingID.DataBindings.Add("Text", bsBindings, "BindingID") 'Bind form control txtOrdinal to the Ordinal field txtOrdinal.DataBindings.Add("Text", bsBindings, "Ordinal") 'Bind form control txtBindingCode to the BindingCode field txtBindingCode.DataBindings.Add("Text", bsBindings, "BindingCode") 'Bind form control txtBindingName to the BindingName Field txtBindingName.DataBindings.Add("Text", bsBindings, "BindingName") End Sub Private Sub New_Bindings(ByVal intOrdinal As Integer, _ ByVal strCode As String, _ ByVal strName As String) drCurrent = MyDataSet.Tables("tblBindings").NewRow() drCurrent("Ordinal") = intOrdinal
I dont think you're doing anything wrong, at least nothing obvious. Just curious, have you actually tried to send the updated dataset to the database to see what happens?
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
-
I dont think you're doing anything wrong, at least nothing obvious. Just curious, have you actually tried to send the updated dataset to the database to see what happens?
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
No, I haven't. In a prior message I tried to add new records to the end of the dataset. I was afraid to commit the updates because the dataset was hosed up. Secondly - I need to add another button to the BindingNavigator control for updating purposes. I have to create a new image(Update) for this new button to indicate that when this button is activated it will commit the updates to the SQL Server database. I also need to write the updating code. I have put this off until I'm satisfied that the data in the dataset is represented properly. Thanks, Quecumber256
-
No, I haven't. In a prior message I tried to add new records to the end of the dataset. I was afraid to commit the updates because the dataset was hosed up. Secondly - I need to add another button to the BindingNavigator control for updating purposes. I have to create a new image(Update) for this new button to indicate that when this button is activated it will commit the updates to the SQL Server database. I also need to write the updating code. I have put this off until I'm satisfied that the data in the dataset is represented properly. Thanks, Quecumber256
Im just curious if the database/dataadapter connection will help resolve the ID issue because you have to think of other situations where a company has 15 apps going all with in memory datasets, how does it resolve the ID's when they're updated? It has to let SQL Server or whichever database handle the "REAL" ID's (or at least I would think so).
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
-
Im just curious if the database/dataadapter connection will help resolve the ID issue because you have to think of other situations where a company has 15 apps going all with in memory datasets, how does it resolve the ID's when they're updated? It has to let SQL Server or whichever database handle the "REAL" ID's (or at least I would think so).
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
I don't think that will be a problem with this particular application. This application is just a tool to help the administrator with data management. For-what-its-worth: Here is the T-SQL script for the table I'm having trouble with. CREATE TABLE Tool_Dev.dbo.tblBindings ([BindingId] INT IDENTITY (1,1) PRIMARY KEY, [BindingCode] CHAR(4) NOT NULL, [Ordinal] INT NOT NULL, [BindingName] VARCHAR(50) NOT NULL) Thanks, Quecumber256