I am trying to add a new record to a database using VB.net. I have used code for one table in the database which worked and then copied and pasted it and changed the column names to the ones in the other table and then I got an error saying that the insert into statement was wrong even though I am using a comand builder.
Here is my code. Please reply telling me my problem.
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim newrow As DataRow
Dim msgvalue As Integer
dbconn.Open()
table_name = "tblpassword"
sql = "SELECT * FROM tblpassword"
Dim ds_test As New DataSet
Dim da_test = New OleDb.OleDbDataAdapter(sql, dbconn) 'gets data from table
Dim cb As New OleDb.OleDbCommandBuilder(da_test) 'creates the sql statement for the insert into row.
da_test.Fill(ds_test, table_name) 'fills the dataset
newrow = ds_test.Tables(table_name).NewRow 'adds collumns to the datarow
If txtpassword.Text = txtComPassword.Text Then
If txtusername.Text = "" Then
MsgBox("please enter a site name", MsgBoxStyle.Information, "Site name")
Exit Sub
ElseIf txtusername.Text.Length <= 50 Then 'makes sure that the username is less than 50 characters for validation of the database
newrow.Item("Username") = txtusername.Text 'adds the text into the datarow
Else
MsgBox("Username is too long!" & ControlChars.NewLine & "Please enter a username of less that 30 characters") 'displays error message
Exit Sub 'exits the subprocedure
End If
If txtpassword.Text.Length <= 15 And txtpassword.Text.Length >= 6 Then 'makes sure that the password is 6 adn 15 characters for validation of the database
newrow.Item("Password") = txtpassword.Text 'adds the text into the datarow
Else
MsgBox("Password is invalid!" & ControlChars.NewLine & "Please enter a username between 5 and 15 characters") 'displays error message
Exit Sub 'exits the subprocedure
End If
If cboaccess.Text.Length = 1 Then 'makes sure that the access level is a number characters for validation of the database
newrow.Item("Access") = cboaccess.Text 'adds the text into the datarow
Else
MsgBox("Access level is invalid!" & ControlChars.NewLine & "Please enter a single number for access level") 'displays error message
Exit Sub 'exits the subprocedure
End If
ds_test.Tables(table_name).Rows.Add(newrow) 'adds the new row into the dataset
da_test.Update(ds_test, table_name) 'updates the database from the database
Else
MsgBox("password's do not match")
End If
End Sub

Have you tried using ds_test.AcceptChanges() before updating the database?
Sometimes you need to commit the changes to the dataset before sending it's updated state back to the database.

Have you tried using ds_test.AcceptChanges() before updating the database?
Sometimes you need to commit the changes to the dataset before sending it's updated state back to the database.

I have tried this and now it doesn't throw an error but just doesn't add it to the database

Have you debugged the code and looked at the property da_test.InsertCommand.CommandText .
It should contain the appropriate SQL query.
Also, you can try putting everything inside a Try...Catch statement to see if the Update command throws any errors.

Have you debugged the code and looked at the property da_test.InsertCommand.CommandText .
It should contain the appropriate SQL query.
Also, you can try putting everything inside a Try...Catch statement to see if the Update command throws any errors.

thats great thanks :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.