I'm trying to insert info into two tables on a single btnclick. Its writing to only one table still. Can't see what I'm missing. [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 10 years ago.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

    If (Not Page.IsPostBack) Then
        Dim strDatabaseNameAndLocation As String
        strDatabaseNameAndLocation = Server.MapPath("databob.mdb")
        Dim strSQLCommand As String
        strSQLCommand = "SELECT Customers.* FROM Customers ORDER BY Customers.CustomerID DESC;"
        Dim objOleDbConnection As System.Data.OleDb.OleDbConnection
        objOleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
        objOleDbConnection.Open()
        Dim objOleDbCommand As System.Data.OleDb.OleDbCommand
        objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
        Dim objOleDbDataReader As System.Data.OleDb.OleDbDataReader
        objOleDbDataReader = objOleDbCommand.ExecuteReader()
        Dim datDataTable As System.Data.DataTable
        datDataTable = New System.Data.DataTable()
        datDataTable.Load(objOleDbDataReader)
        objOleDbConnection.Close()
    End If


    If (Not Page.IsPostBack) Then
        Dim strDatabaseNameAndLocation As String
        strDatabaseNameAndLocation = Server.MapPath("databob.mdb")
        Dim strSQLCommand2 As String
        strSQLCommand2 = "SELECT CardType, CardNumber, Valid, Expiry, 3Digit FROM Orders ORDER BY Orders.OrderID DESC;"
        Dim objOleDbConnection As System.Data.OleDb.OleDbConnection
        objOleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
        objOleDbConnection.Open()
        Dim objOleDbCommand As System.Data.OleDb.OleDbCommand
        objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand2, objOleDbConnection)
        Dim objOleDbDataReader As System.Data.OleDb.OleDbDataReader
        objOleDbDataReader = objOleDbCommand.ExecuteReader()
        Dim datDataTable As System.Data.DataTable
        datDataTable = New System.Data.DataTable()
        datDataTable.Load(objOleDbDataReader)
        objOleDbConnection.Close()
    End If

End Sub

Protected Sub btnContinue_Click(ByVal sender As Object, ByVal e As System.EventArgs)

    Dim strFirstName As String
    Dim strLastName As String
    Dim strAddress As String
    Dim strPostcode As String
    Dim strHomeNo As String
    Dim strMobileNo As String
    Dim strEmail As String
    Dim strCardType As String
    Dim strCardNumber As String
    Dim strValid As String
    Dim strExpiry As String
    Dim str3Digit As String
    strFirstName = tbxFirstName.Text
    strLastName = tbxLastName.Text
    strAddress = tbxAddress.Text
    strPostcode = tbxPostcode.Text
    strHomeNo = tbxHomeNo.Text
    strMobileNo = tbxMobileNo.Text
    strEmail = tbxEmail.Text
    strCardType = ddlCardType.Text
    strCardNumber = tbxCardNumber.Text
    strValid = tbxValid.Text
    strExpiry = tbxExpiry.Text
    str3Digit = tbx3Digit.Text

    Dim strDatabaseNameAndLocation As String
    strDatabaseNameAndLocation = Server.MapPath("databob.mdb")
    Dim strSQLCommand As String
    strSQLCommand = "INSERT INTO Customers(FirstName, LastName, Address, Postcode, HomeNo, MobileNo, Email) " & _
    "Values ('" & strFirstName & "', '" & strLastName & "', '" & strAddress & "', '" & strPostcode & "', '" & strHomeNo & "', '" & strMobileNo & "', '" & strEmail & "');"
    Dim strSQLCommand2 As String
    strSQLCommand2 = "INSERT INTO Orders(CardType, CardNumber, Valid, Expiry, 3Digit) " & _
        "Values ('" & strCardType & "', '" & strCardNumber & "', '" & strValid & "', '" & strExpiry & "', '" & str3Digit & "');"
    Dim objOleDbConnection As System.Data.OleDb.OleDbConnection
    objOleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
    objOleDbConnection.Open()
    Dim objOleDbCommand As System.Data.OleDb.OleDbCommand
    objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
    objOleDbCommand.ExecuteNonQuery()
    objOleDbConnection.Close()
    strSQLCommand = "SELECT Customers.* FROM Customers ORDER BY Customers.CustomerID DESC;"
    strSQLCommand2 = "SELECT Orders.* FROM Orders ORDER BY Orders.OrderID DESC;"
    objO开发者_运维知识库leDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
    objOleDbConnection.Open()
    objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
    Dim objOleDbDataReader As System.Data.OleDb.OleDbDataReader
    objOleDbDataReader = objOleDbCommand.ExecuteReader()
    Dim datDataTable As System.Data.DataTable
    datDataTable = New System.Data.DataTable()
    datDataTable.Load(objOleDbDataReader)
    objOleDbConnection.Close()
    tbxFirstName.Text = ""
    tbxLastName.Text = ""
    tbxAddress.Text = ""
    tbxPostcode.Text = ""
    tbxHomeNo.Text = ""
    tbxMobileNo.Text = ""
    tbxEmail.Text = ""
    ddlCardType.Text = ""
    tbxCardNumber.Text = ""
    tbxValid.Text = ""
    tbxExpiry.Text = ""
    tbx3Digit.Text = ""



End Sub

---

objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
objOleDbCommand.ExecuteNonQuery() 

That executes the first one but you do not do it again for strSQLCommand2

-- as an aside please look into parameterization of your queries. You are just asking for sql injection with that.

---

INSERT INTO Orders(Orders( looks a tad fishy to me (unless that's a C&P error posting this question)

---

And as Ken points out, if you're wanting to run both queries (rather than replace one with the other), you probably want:

strSQLCommand = strSQLCommand & " INSERT INTO Orders(CardType, CardNumber, Valid, Expiry, 3Digit) " & _

---

Your problem is that you are trying to use same string variable to hold both sqls, in fact you are overwriting the first one with the second one, modify your code like this

Dim strSQLCommand As String

Dim strSQLCommand2 As String

strSQLCommand = "INSERT INTO Customers(FirstName, LastName, Address, Postcode, HomeNo, MobileNo, Email) " & _ "Values ('" & strFirstName & "', '" & strLastName & "', '" & strAddress & "', '" & strPostcode & "', '" & strHomeNo & "', '" & strMobileNo & "', '" & strEmail & "');" strSQLCommand2 = "INSERT INTO Orders(Orders(CardType, CardNumber, Valid, Expiry, 3Digit) " & _ "Values ('" & strCardType & "', '" & strCardNumber & "', '" & strValid & "', '" & strExpiry & "');"

and then afterwards you need to execute both statements, also you should add a transaction, something like this

    objOleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
objOleDbConnection.Open()
Dim objTrans As System.Data.OleDb.OleDbTransaction;

objTrans=objOleDbConnection.BeginTransaction();
try
{
  Dim objOleDbCommand As System.Data.OleDb.OleDbCommand
  objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
  objOleDbCommand.ExecuteNonQuery()
  objOleDbCommand.CommandText =strSQLCommand2;
  objOleDbCommand.ExecuteNonQuery()
  objTrans.Commit();
}
catch{Exception ex}
{
  objTrans.Rollback();
}
finally
{
   objOleDbConnection.Close()
}

Just pulling it out of my head, can be a typo on it, but you can get the idea