1

I am trying to execute two different INSERT statements with one click of a button.

But when I try running my code only one of the INSERT statements is working at time.

What is the best way to fix this?

pro = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\Users\XXXX\Desktop\XXXX\XXXXX.mdb" connstring = pro myconnection.ConnectionString = connstring myconnection.Open() commmand = ("insert into ApplicationData ([lastname], [firstname],[studentbirthday],[gender], [email], [phonenumber], [address], [city], [state], [zip], [dadlastname], [dadfirstname], [momlastname],[momfirstname]) values ('" & NewLastNameText.Text & "', '" & NewFirstNameText.Text & "','" & NewDateTimePicker.Text & "','" & NewGenderText.Text & "','" & NewEmailText.Text & "','" & phone.Text & "','" & NewAddressText.Text & "','" & city.Text & "','" & state.Text & "','" & zip.Text & "','" & NewDadLNtext.Text & "','" & NewDadFNtext.Text & "','" & NewMomLNtext.Text & "','" & NewMomFNtext.Text & "')") commmand = ("insert into StudentLogin ([username], [password]) values('" & username.Text & "','" & password.Text & "')") Dim cmd As OleDbCommand = New OleDbCommand(commmand, myconnection) cmd.Parameters.Add(New OleDbParameter("lastname", CType(NewLastNameText.Text, String))) cmd.Parameters.Add(New OleDbParameter("firstname", CType(NewFirstNameText.Text, String))) cmd.Parameters.Add(New OleDbParameter("studentbirthday", CType(NewDateTimePicker.Text, String))) cmd.Parameters.Add(New OleDbParameter("gender", CType(NewDateTimePicker.Text, String))) cmd.Parameters.Add(New OleDbParameter("email", CType(NewEmailText.Text, String))) cmd.Parameters.Add(New OleDbParameter("phonenumber", CType(phone.Text, String))) cmd.Parameters.Add(New OleDbParameter("address", CType(NewAddressText.Text, String))) cmd.Parameters.Add(New OleDbParameter("city", CType(city.Text, String))) cmd.Parameters.Add(New OleDbParameter("state", CType(state.Text, String))) cmd.Parameters.Add(New OleDbParameter("zip", CType(zip.Text, String))) cmd.Parameters.Add(New OleDbParameter("dadlastname", CType(NewDadLNtext.Text, String))) cmd.Parameters.Add(New OleDbParameter("dadfirstname", CType(NewDadFNtext.Text, String))) cmd.Parameters.Add(New OleDbParameter("momfirstname", CType(NewMomLNtext.Text, String))) cmd.Parameters.Add(New OleDbParameter("momlastname", CType(NewMomFNtext.Text, String))) cmd.Parameters.Add(New OleDbParameter("username", CType(username.Text, String))) cmd.Parameters.Add(New OleDbParameter("password", CType(password.Text, String))) Try cmd.ExecuteNonQuery() cmd.Dispose() myconnection.Close() MsgBox("Student Added") NewLastNameText.Clear() NewFirstNameText.Clear() NewEmailText.Clear() NewAddressText.Clear() NewDadLNtext.Clear() NewDadFNtext.Clear() NewMomLNtext.Clear() NewMomFNtext.Clear() Catch ex As Exception End Try
Improve this question
2
  • 1
    The second assignment to the command variable overwrites the first one. If you want to run multiple insert statements, just separate them with semicolons. Also, you really should look at parameterizing your queries, see here: stackoverflow.com/questions/17867192/… Commented Feb 10, 2019 at 18:03
  • You probably mean two different tables, not two different databases. Commented Feb 10, 2019 at 18:04

2 Answers 2

Reset to default
1

Put both commands into the same string

Dim command1 = "insert into ApplicationData ([lastname], ... values (?, ?, ...)" Dim command2 = "insert into StudentLogin ([username], ... values (?, ?, ...)" commmand = command1 & "; " & command2

Btw.: you are adding parameters (which is fine), but did not replace the string concatenation of the commands by parameters. For OLEDB, you have to use positional parameters. I.e., in the SQL text, you have to use a ? for each parameter. Then you have to add the parameters to the parameter collection in the same order! (The name you are using there is ignored, so it does not matter.)

Pass the connection string to the connection when creating it and do not change it afterwards. Always declare the connection in a Using Statement. It automatically closes and disposes the connection at the end. Note, it is not a problem to create new connection objects every time you use one. Because of connection pooling, the "real" connection will be reused.

pro = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\Users\XXXX\Desktop\XXXX\XXXXX.mdb" Using myconnection As New OleDbConnection(pro) myconnection.Open() Dim command1 = "insert into ApplicationData ([lastname], ... values (?, ?, ...)" Dim command2 = "insert into StudentLogin ([username], ... values (?, ?, ...)" commmand = command1 & "; " & command2 ... End Using ' Automatically closes connection here.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I was under the impression that Access can only execute single statements and will error if characters are encountered after the semicolon.
@Mary, if this should be the case, you would have to execute ExecuteNonQuery twice with two commands, or reuse the same command object, but take care to clear the parameters collection before adding the parameters for the second insert.
0

OleDb does not care about the names or our parameters. It only cares about the order they appear in the Sql statment matches the order they are added to the parameters collection.

Concatenating strings in you Sql statement is a bad idea for several reason and is certainly not needed when you are using parameters. The .Add method of the parameters collection is very clever and returns an OleDb parameter object without us having to declare on explicitly. It is always a good idea to include the OleDb data type.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click 'Pass the connection string directly to the constructor of the connection Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\Users\XXXX\Desktop\XXXX\XXXXX.mdb") 'Pass the Sql statement and the connection directly to the constructor of the command. 'Note: this should NOT be an open connection. Using StudentCommand As New OleDbCommand("insert into ApplicationData ([lastname], [firstname],[studentbirthday],[gender], [email], [phonenumber], [address], [city], [state], [zip], [dadlastname], [dadfirstname], [momlastname],[momfirstname]) values (lastname, firstname,studentbirthday,gender,email,phonenumber,address,city,state,zip,dadlastname,dadfirstname,momlastname,momfirstname);", cn) StudentCommand.Parameters.Add("lastname", OleDbType.VarChar).Value = NewLastNameText.Text StudentCommand.Parameters.Add("firstname", OleDbType.VarChar).Value = NewFirstNameText.Text StudentCommand.Parameters.Add("studentbirthday", OleDbType.VarChar).Value = NewDateTimePicker.Text StudentCommand.Parameters.Add("gender", OleDbType.VarChar).Value = NewDateTimePicker.Text StudentCommand.Parameters.Add("email", OleDbType.VarChar).Value = NewEmailText.Text StudentCommand.Parameters.Add("phonenumber", OleDbType.VarChar).Value = phone.Text StudentCommand.Parameters.Add("address", OleDbType.VarChar).Value = NewAddressText.Text StudentCommand.Parameters.Add("city", OleDbType.VarChar).Value = city.Text StudentCommand.Parameters.Add("state", OleDbType.VarChar).Value = state.Text StudentCommand.Parameters.Add("zip", OleDbType.VarChar).Value = zip.Text StudentCommand.Parameters.Add("dadlastname", OleDbType.VarChar).Value = NewDadLNtext.Text StudentCommand.Parameters.Add("dadfirstname", OleDbType.VarChar).Value = NewDadFNtext.Text StudentCommand.Parameters.Add("momfirstname", OleDbType.VarChar).Value = NewMomLNtext.Text StudentCommand.Parameters.Add("momlastname", OleDbType.VarChar).Value = NewMomFNtext.Text 'Open the connection at the last minute cn.Open() StudentCommand.ExecuteNonQuery() cn.Close() End Using 'Disposes StudentCommand Using LoginCommand As New OleDbCommand("insert into StudentLogin ([username], [password]) values(@username, @password;", cn) LoginCommand.Parameters.Add("@username", OleDbType.VarChar).Value = username.Text LoginCommand.Parameters.Add("@password", OleDbType.VarChar).Value = password.Text cn.Open() LoginCommand.ExecuteNonQuery() 'We don't need to .Close the connection 'The second End Using will close and dispose the connection End Using 'Disposes LoginCommand End Using MessageBox.Show("Student Added") NewLastNameText.Clear() NewFirstNameText.Clear() NewEmailText.Clear() NewAddressText.Clear() NewDadLNtext.Clear() NewDadFNtext.Clear() NewMomLNtext.Clear() NewMomFNtext.Clear() End Sub

One button click, 2 commands executed.

Of course in a real application you would NEVER save passwords as plain text.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.