Writing to Access

[cca05]

what's required to work with the obj.executenonquery command in order to write to an access database.

I've included

Imports System.Data.OleDb

at the top of the form.

Also I've created a dbase connection, which I opened. I'm attepting to use the submit button to execute the executenonquery command.

Below is the code.

 Imports System.Data.OleDb 
 
Public Class frmCrewChief
 
Inherits System.Windows.Forms.Form
 
#Region " Windows Form Designer generated code "
 
{...}
 
#End Region
 
Private Sub FrmCrewChief_Load(ByVal sender As System.Object, _
 
ByVal e As System.EventArgs) HandlesMyBase.Load
 
btnSave.Enabled = False
 
End Sub
 
Private Sub btnEnter_Click(ByVal sender As System.Object, _
 
ByVal e As System.EventArgs) Handles btnEnter.Click
 
' transferring entries to Que from Form Fields
 
lblCheck.Text = "Please check your entries, then submit."
 
lblDisEmp.Text = txtEmp.Text
 
lblDisDate.Text = dteTimePkr.Text
 
lblDisCCLast.Text = txtCCLast.Text
 
lblDisCCFirst2.Text = txtCCFirst.Text
 
lblDisAMT1Last.Text = txtAmtLast1.Text
 
lblDisAMT2Last.Text = txtAmtLast2.Text
 
lblDisAMT3Last.Text = txtAmtLast3.Text
 
lblDisAMT1First.Text = txtAmtFirst1.Text
 
lblDisAMT2First.Text = txtAmtFirst2.Text
 
lblDisAMT3First.Text = txtAmtFirst3.Text
 
lblDisQtyAmts.Text = cmbNumAmts.Text
 
lblDisAcft.Text = txtAcft.Text
 
lblDisTerm.Text = cmbTerm.Text
 
lblDisGate.Text = txtGate.Text
 
lblDisScratch.Text = txtScratch.Text
 
btnSave.Enabled = True
 
btnEnter.Enabled = False
 
End Sub
 
Private Sub btnSave_Click(ByVal sender As System.Object, _
 
ByVal e As System.EventArgs) Handles btnSave.Click
 
' create dbase connection
 
oleDbConnection.Open()
 
objChkSubmit.ExecuteNonQuery()
 
oleDbConnection.Close()
 
End Sub
 
End Class

 

[kulrom]

you have done a half of your work ... you just need to add SQL Statement/Query.

 oleDbConnection.Open()

strSQL = "INSERT INTO database1 (value1, value2) VALUES (param1, param2)"
 
objChkSubmit = new oledbcommand(strSQL, oleDbConnection)

objChkSubmit.ExecuteNonQuery()
 
oleDbConnection.Close()

Cheers

 

[cca05]

strSQL

Where do i declare the strSQL variable? in the private sub, and how do I declare it?

thanks,
chris

 

[akhhttar]

you can declare strSQL variable as datatype string, same like other string variables declareation
e.g
dim strSQL as string
...
and you can declare in anyware in your program..but it will be better if you declare it in Sub, where you are executing executeNonQuery() function

 

[cca05]

The program breaks and throws this error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

Line highlighted is

objChkSubmit.ExecuteNonQuery()

I've set up the SQL statement in the query builder and assigned values to dbase there as objChkSubmit, so should I not use the strSQL statement?
Dim strSQL As String

' create dbase connection

oleDbConnection.Open()

' execute ExecuteNonQuery

' sql statement to load into fields

objChkSubmit = New OleDbCommand(strSQL, oleDbConnection)

objChkSubmit.ExecuteNonQuery()

' close dbase connection

oleDbConnection.Close()

btnEnter.Enabled = True

btnSave.Enabled = False

 

[cca05]

Latest Code attempt, I'm sleeping on it now, thanks.

This is the latest code I've tried, I have no idea what I'm doing, but I'd sure like to learn

Private Sub btnSave_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnSave.Click

Dim strSQL As String = _

"INSERT INTO dfwCrew_Chief (emp_num) VALUES (lblDisEmp.Text)"

Dim objSubmit As OleDbCommand

' create dbase connection

oleDbConnection.Open()

' execute ExecuteNonQuery

' sql statement to load into fields

objSubmit = New OleDbCommand(strSQL, oleDbConnection)

objSubmit.ExecuteNonQuery()

' close dbase connection

oleDbConnection.Close()

btnEnter.Enabled = True

btnSave.Enabled = False

End Sub

 

[Paszt]

Ah yes, the old including VB.NET code in the SQL statement.
You need to concatenate strSQL in order to evaluate the statement:

Dim strSQL As String = _
"INSERT INTO dfwCrew_Chief (" & emp_num & ") VALUES (" & lblDisEmp.Text & ")"

I'm assuming emp_num is a variable in your app.
Also note that ExecuteNonQuery returns the number of rows affected. You could assign a variable to the Function and use it's value to verify the operation did as you expected.

 

[kulrom]

Well, i suggest to take a look at the attached project below that i've just adapted for you ... Cheers

 

[Rat]

Well, i suggest to take a look at the attached project below that i've just adapted for you ... Cheers

Great example bro, i'm saving it ... i'm sure i will have some use for it later.