What am I doing wrong?

J

joe_pool_is

Contributor
Joined
Jan 18, 2004
Messages
507
Location
Longview, TX [USA]
This is driving me nuts. Code looks good. Database appears to match. SQL statement looks good. Still, I get:
Code: 
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:
Code: 
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.
 

Attachments

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


Visual Basic: 
  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
 
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 said:
is the connection open before trying to set parameters???
Click to expand...
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).
 
change:
Visual Basic: 
Dim p As System.Data.OleDb.OleDbParameter

For Each p In Command1.Parameters

     p.Value = Convert.DBNull

Next

to
Visual Basic: 
Dim i = as integer 
For i = 0 to 4 do
     Command1.Parameters.Add(Convert.DBNull)
Next
 
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.
 
try this. . .

Visual Basic: 
Dim i = as integer 

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

building sql is poor practice. . .

parameterize!!!
 
Back
Top