Help with System.Data.OleDb.OleDbException

[lakshya]

This code is alright but doesn't work. It throws exception at runtime "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.windows.forms.dll"

The code on button click is:

Me.OleDbInsertCommand1 = New OleDbCommand("INSERT INTO Enquiry(Enq_ID, Cand_Name, Cand_Addr, Cand_City, Cand_Cno, Cand_Mail, Cand_Qlf, Enq_Course, Cand_Ref, Enq_Date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Me.OleDbConnection2)
With Me.OleDbInsertCommand1.Parameters
If IsNumeric(TextBox2.Text.Trim) = True Then
.Add(New OleDbParameter("Cand_Cno", OleDbType.Integer)).Value = Integer.Parse(TextBox4.Text.Trim)
End If
If IsDate(DateTimePicker1.Value) = True Then
.Add(New OleDbParameter("Enq_Date", OleDbType.DBDate)).Value = Date.Parse(DateTimePicker1.Value)
End If
.Add(New OleDbParameter("Enq_ID", OleDbType.VarWChar)).Value = TextBox1.Text.Trim
.Add(New OleDbParameter("Cand_Name", OleDbType.VarWChar)).Value = TextBox2.Text.Trim
.Add(New OleDbParameter("Cand_Addr", OleDbType.VarWChar)).Value = TextBox3.Text.Trim
.Add(New OleDbParameter("Cand_City", OleDbType.VarWChar)).Value = TextBox6.Text.Trim
.Add(New OleDbParameter("Cand_Mail", OleDbType.VarWChar)).Value = TextBox5.Text.Trim
.Add(New OleDbParameter("Cand_Qlf", OleDbType.VarWChar)).Value = TextBox8.Text.Trim
.Add(New OleDbParameter("Enq_Course", OleDbType.VarWChar)).Value = TextBox7.Text.Trim
.Add(New OleDbParameter("Cand_Ref", OleDbType.VarWChar)).Value = TextBox9.Text.Trim
End With
OleDbConnection2.Open()
OleDbInsertCommand1.ExecuteNonQuery() 'This command will insert the data.
MsgBox("Data Entered Successfully")
OleDbConnection2.Close()

 

[ashishnaicker]

You are using the correct oledbtypes. But why are you then parsing them to become strings again?

 

[sfrandolph]

What kind of database are you running against? MS-SQL, Oracle?

 

[vis781]

If IsNumeric(TextBox2.Text.Trim) = True

Then
.Add(New OleDbParameter("Cand_Cno", OleDbType.Integer)).Value = Integer.Parse(TextBox4.Text.Trim)
EndIf
If IsDate(DateTimePicker1.Value) = TrueThen
.Add(New OleDbParameter("Enq_Date", OleDbType.DBDate)).Value = Date.Parse(DateTimePicker1.Value)
EndIf

My guess would be that your problem is in this part. When those 'IF' statements execute and add the parameters to the parameters collection, they are out of sequence with the order that they appear in, in the SQL statement.

 

[ashishnaicker]

the IF statements are OK. Just that they are not being executed as the value in the textbox is not numeric. Should implement an else clause there.

 

[vis781]

I didn't say there was anything wrong with the IF statements. Other than you could use a much more elegant method i.e..

Integer.TryParse
Date.TryParse

That is to say nothing about the fact that validation should occur way before trying to add parameters to an oledb query.

The problem from what i can see is that parameters must be added to the collection in the order that they appear in the SQL statement, in lakshya's code they won't.

 

[ashishnaicker]

oops.. sorry overlooked the first code.