Insert command problem....need help 1

[smithbr]

I am having a problem inserting data into a database. I had it working yesterday and when I can in today it would not work. It works sometimes when i click the add button twice real quick but does not work if I only click it once or twice slowly. Does anyone see anything in my code that I could change to make it work on one click all the time...I really need help on this..I have to have it working by the end of the day today...Any help is appreciated more than you may every know.

Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click

Dim strCommandText As String
strCommandText = "INSERT INTO InvoiceHistory (InvoiceDate,InvoiceNumber,LineSEQNo,CustomerNumber,ItemNumber,ProductLine,QuantityShipped,LastUnitPrice,DiscountP, PONumber) VALUES ("
strCommandText += "'" + lbldatedisp.Text + "',"
strCommandText += txtinvnum.Text + ","
strCommandText += "'" + txtrefnum.Text + "',"
strCommandText += "'" + lblcustdisp.Text + "',"
strCommandText += "'" + cbotype.Items(cbotype.SelectedIndex) + "',"
strCommandText += "'" + Mid$(cboprodln.Items(cboprodln.SelectedIndex), Len(cboprodln.Items(cboprodln.SelectedIndex)) - 3) + "',"
strCommandText += "1," ' QuantityShipped
strCommandText += txtdeduct.Text + ","
strCommandText += "'0'," ' Discount
strCommandText += "'" + lblponumber.Text + "')"

cmdAddInventory.CommandText = strCommandText
Try
OleDbConnection1.Open()
cmdaddinventory.ExecuteNonQuery()
Catch Ex As OleDbException
System.Diagnostics.Debug.Write(Ex.Message)
End Try

OleDbConnection1.Close()

cmdfind_Click(sender, e)
cmdclear_Click(sender, e)

End Sub

 

[BradRoberts56]

At the bottom of the code try using
(your button name).PerformClick() instead of passing references to the other event handlers that were passed into this one. That may be causing a problem.

 

[PankajBanga]

TRY THIS !!!

Create the following subroutines

Private Sub FindRec()
'Your Find functionallity

End Sub


Private Sub ClearRec()
'Your Clear functionallity

End Sub

Private Sub AddData()
'String variable to hold connectionString.
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourPath\yourDB.mdb"

'Create an oleDbConnection object,
'and then pass in the ConnectionString to the constructor.
Dim conn As OleDbConnection = New OleDbConnection(connectionString)

Dim insertString As String 'Variable to hold the SQL statement.
Dim cmd As OleDbCommand 'Create an OleDbCommand object.
Dim insertResult As Integer 'Variable to hold result of ExecuteNonQuery method.

Try
'Open connection.
conn.Open()

'Initialize SQL string.
'Tweak the insertString as per your requirement.
insertString = "INSERT INTO yourDBTableName (yourInsertField1, yourInsertField2,....)" & _
" VALUES ( " & """" & cbotype.SelectedItem & """" & "," & """" & txtrefnum.Tetx & """" ... & &quot"

'Initialize OleDbCommand object.
cmd = New OleDbCommand(insertString, conn)

'Send the CommandText to the connection, execute the Command.
insertResult = cmd.ExecuteNonQuery

Catch Excep As System.Exception
MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally
'Close connection.
conn.Close()
End Try
End Sub

On Click event of Find Button & Clear Button call FindRec & ClearRec subroutine respectivelly.

On Click event of Add Button call AddData() then FindRec() & ClearRec()

Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click

'Call AddData subroutine
AddData()

'Call FindRec subroutine
FindRec()

'Call ClearRec subroutine
ClearRec()

End Sub

It is always a good practice to use "&" for concatenation rather than "+". I always wrap by string values in Insert or Update string with """" cos sometime we might get single quotes (') in our string like "Mc’Millar", "Mc’Gregor" etc. Using """" will prevent insert query from crashing.