What am I doing wrong?

[joe_pool_is]

This is driving me nuts. Code looks good. Database appears to match. SQL statement looks good. Still, I get:

In the Immediate Window:
?sql
"INSERT INTO Legal1 (FirstName, MiddleName, LastName, Date, Signature) VALUES ('Timmy', 'Joe', 'Pool, Jr.', '8/10/2004 5:39:47 PM', 'Poojo');"
?ex.Message
"Syntax error in INSERT INTO statement."

 

My database is attached, and my code is here:

Dim i As Integer = 0
Dim strDB As String, strConnection As String
Dim strName() As String
strName = txtPurchaserName.Text.Split(Convert.ToChar(" "))
strDB = Server.MapPath("database.mdb")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";Persist Security Info=False"
Select Case UBound(strName, 1)
Case 0	 ' // Only one word entered. Used as Last name
 SQL = "INSERT INTO Legal1 (LastName, Date, Signature) " & _
 "VALUES ('" & strName(0) & "', '" & _
 CStr(DateTime.Now) & "', '" & txtPurchaserSign.Text & "');"
Case 1	 ' // assumes First and Last names entered
	SQL = "INSERT INTO Legal1 (FirstName, LastName, Date, Signature) " & _
		 "VALUES ('" & strName(0) & "', '" & strName(1) & "', '" & _
		 CStr(DateTime.Now) & "', '" & txtPurchaserSign.Text & "');"
Case 2	 ' // assumes First, Middle, and Last names entered
	SQL = "INSERT INTO Legal1 (FirstName, MiddleName, LastName, Date, Signature) " & _
		 "VALUES ('" & strName(0) & "', '" & strName(1) & "', '" & strName(2) & "', '" & _
		 CStr(DateTime.Now) & "', '" & txtPurchaserSign.Text & "');"
Case Else ' // assumes First, Middle, Last and prefixes entered
	For i = 3 To UBound(strName)	' // appends the rest to the Last name
		strName(2) &= " " & strName(i)
	Next
	SQL = "INSERT INTO Legal1 (FirstName, MiddleName, LastName, Date, Signature) " & _
		 "VALUES ('" & strName(0) & "', '" & strName(1) & "', '" & strName(2) & "', '" & _
		 CStr(DateTime.Now) & "', '" & txtPurchaserSign.Text & "');"
End Select
Try
Command1 = New OleDbCommand
Command1.Connection = New OleDbConnection(strConnection)
Command1.Connection.Open()
Command1.CommandText = SQL
Command1.ExecuteNonQuery()
Command1.Connection.Close()
Catch ex As Exception	' // there were errors
Command1.Connection.Close()
lblError.Visible = True
lblError.Text = "Unable to save/update signature and date to database. Please contact Webmaster."
Exit Sub
End Try

It bombs on "Command1.ExecuteNonQuery()" every time with the message listed at the top.

 

Could anyone help me? It's probably a stupid mistake. Maybe a type, but I just can't see it.

database.zip

[eramgarden]

can u grab that SQL ( the one built by the code) and run it directly in QA or whatever database you;re using?

[PlausiblyDamp]

Not got SQL handy to test but it may be failing on the field Date (it's a keyword) - try using [Date] instead.

[Joe Mamma]

try with parameters:

 

 

  Sub TestSQL()
  Dim i As Integer = 0
  Dim strDB As String, strConnection As String
  Dim strName() As String
  strName = txtPurchaserName.Text.Split(Convert.ToChar(" "))
  strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb;Persist Security Info=False"
  Dim sql As String = "INSERT INTO Legal1 (FirstName, MiddleName, LastName, Date, Signature) " _
    & "VALUES (?, ?, ?, ?, ?);"
  Try
   Dim Conn As OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(strConnection)
   Conn.Open()
   Command1 = New System.Data.OleDb.OleDbCommand(sql, Conn)
   Dim p As System.Data.OleDb.OleDbParameter
   For Each p In Command1.Parameters
    p.Value = Convert.DBNull
   Next
   Select Case UBound(strName, 1)
    Case 0
     ' // Only one word entered. Used as Last name
     Command1.Parameters(2).Value = strName(0)
     Command1.Parameters(3).Value = CStr(DateTime.Now)
     Command1.Parameters(4).Value = txtPurchaserSign.Text"
    Case 1     ' // assumes First and Last names entered
     Command1.Parameters(0).Value = strName(0)
     Command1.Parameters(2).Value = strName(1)
     Command1.Parameters(3).Value = CStr(DateTime.Now)
     Command1.Parameters(4).Value = txtPurchaserSign.Text
    Case 2     ' // assumes First, Middle, and Last names entered
     Command1.Parameters(0).Value = strName(0)
     Command1.Parameters(1).Value = strName(1)
     Command1.Parameters(2).Value = strName(2)
     Command1.Parameters(3).Value = CStr(DateTime.Now)
     Command1.Parameters(4).Value = txtPurchaserSign.Text
    Case Else       ' // assumes First, Middle, Last and prefixes entered
     For i = 3 To UBound(strName)       ' // appends the rest to the Last name
      strName(2) &= " " & strName(i)
     Next
     Command1.Parameters(0).Value = strName(0)
     Command1.Parameters(1).Value = strName(1)
     Command1.Parameters(2).Value = strName(2)
     Command1.Parameters(3).Value = CStr(DateTime.Now)
     Command1.Parameters(4).Value = txtPurchaserSign.Text
   End Select
   Command1.ExecuteNonQuery()
   Command1.Connection.Close()
  Catch ex As Exception    ' // there were errors
   Command1.Connection.Close()
   lblError.Visible = True
   lblError.Text = "Unable to save/update signature and date to database. Please contact Webmaster."
   Exit Sub
  End Try
 End Sub

[joe_pool_is]

Lots of ideas and suggestions. Let me answer them each in turn:

 

1) eramgarden: I am running a MS Access database. I was able to create an INSERT query and run it directly from the MS Access program (after changing the Date column to read ClickDate). When it ran, I was first notified that I was about to run an append query that would modify the data in the table, which I had to either confirm was okay or abort. After confirming, I got a second MessageBox telling me that I was about to append 1 row, which I also had to confirm that this is what I wanted to do. After confirming those two MessageBoxes, I opened my table, and my data was there. Do I need something in my VB.NET program that responds to these Microsoft MessageBoxes??? If so, how would I do that? It looks like my SQL statement works.

 

2) PlausiblyDamp: I changed the Date keyword to ClickDate in the database and in the SQL command. I still get "Syntax error in INSERT INTO statement." Maybe this is a MS Access database problem instead of an error in my SQL statement, but I don't know what that would be. Any ideas?

 

3) Joe Mamma: Your idea looked interesting, but I tried it last (since it required the greatest change to my code). When the program got to the point where it needed to fill the command parameters, it bombed. Running the "Case Else" Select routine, Command1.Parameters(0).Value = strName(0) produced the following Exception in the "Command Window - Immediate":

?ex.Message

"Invalid index 0 for this OleDbParameterCollection with Count=0."

Your version does look interesting, and I would like to impliment it. Do you have any idea what this error is and how to patch it?

 

I'm still working on this problem. If anyone has any additional input or suggestions, I am all ears.

[Joe Mamma]

is the connection open before trying to set parameters???

[joe_pool_is]

is the connection open before trying to set parameters???

I used your code almost to the letter, since it fit so well with mine. So, yes, the connection is opened via your Conn.Open() line. I was thinking that maybe the Command1.Parameters needed to be innitialized first, similar to dimensioning an array before adding elements to it. Could this be the case?

 

I still don't understand why Access doesn't like my SQL statement when run through VB.NET. It works fine when I create an Insert Query (other than those two MessageBoxes).

[Joe Mamma]

change:

Dim p As System.Data.OleDb.OleDbParameter

For Each p In Command1.Parameters

    p.Value = Convert.DBNull

Next 

 

to

Dim i = as integer 
For i = 0 to 4 do
    Command1.Parameters.Add(Convert.DBNull)
Next 

[joe_pool_is]

Thanks Joe Mamma. I got it to working. The code didn't like your Convert.DBNull, and I couldn't get it to accept ... anything else. So, instead, I constructed each field separately, since there were only 4 anyway.

 

Thanks for your help. Case closed.

[Joe Mamma]

try this. . .

 

Dim i = as integer 

For i = 0 to 4 do
    Command1.Parameters.Add(new OleDbParameter())
Next 

 

building sql is poor practice. . .

 

parameterize!!!