Inserting Data from VB Form to Access Table

[jmancuso]

I'm relatively new to working with VB .NET, especially working with data sets and updating databases.

I'm having a heck of a time trying to take a value entered by the user in a form and write this to an Access table. I've tried at least 15 different things pulling information from MSDN, web, etc. and can't seem to find an answer, the code I've written has made sense and will often execute but not write anything at all to the table.

The table I'm writing to has an autonumber.

Could any of you point me in the direction of where I can find a walk through?

Also, I'm planning on upgrading Access tables to SQL Server, will this have a huge impact?

Here is the most recent code I've tried that doesn't work:

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

'Save data to transaction value table

Dim i, intCode, intField_id As Integer

Dim strDataSave(), strSQL As String

Dim dtCurrentDate As Date

Dim da As System.Data.OleDb.OleDbDataAdapter

Dim cmd As System.Data.OleDb.OleDbCommand

dtCurrentDate = Now 'Timestamp

For i = 0 To 14

If CurrentInfo(i) <> "" Then

'Identify the field Code

strDataSave = Split(CurrentInfo(i), "|")



'Define SQL string to populate current data table

cmd = New System.Data.OleDb.OleDbCommand("INSERT INTO tbl_CurrentData(Currentdata_date, currentdata_value, field_id, loan_num" & _

"VALUES ('" & dtCurrentDate & "','" & strDataSave(0) & "','" & strDataSave(1) & "','" & strLoanNum & "')", ODBC)

cmd.Parameters.Add("'" & dtCurrentDate & "'", System.Data.OleDb.OleDbType.DBDate, 0, "currentdata_date")

cmd.Parameters.Add("'" & strDataSave(0) & "'", System.Data.OleDb.OleDbType.VarWChar, 50, "currentdata_value")

cmd.Parameters.Add("'" & strDataSave(1) & "'", System.Data.OleDb.OleDbType.SmallInt, 0, "field_id")

cmd.Parameters.Add("'" & strLoanNum & "'", System.Data.OleDb.OleDbType.VarWChar, 10, "loan_num")

da.InsertCommand = cmd

End If

Next

End Sub

 

[TPM]

You don't need the dataadapter, change it for a connection. Then open the connection (connection.open) execute the command (cmd.executenonquery) then close the connection (connection.close)

When you specify the NEW oledbcommand include the connection ie
cmd = New (System.Data.OleDb.OleDbCommand("INSERT INTO tbl_CurrentData(Currentdata_date, currentdata_value, field_id, loan_num" & _
"VALUES ('" & dtCurrentDate & "','" & strDataSave(0) & "','" & strDataSave(1) & "','" & strLoanNum & "')", ODBC), connection)

You also don't need to add parameters when you've manually added them.

TPM

 

[jmancuso]

Worked perfectly once I did this. Thank you.

 

[TPM]

Sure thing.

I forgot to say that upgrading to SQL will make a bit of a difference. You can use the sqlclient instead of Ole. Also the types in SQL are a bit different, eg there's not an autonumber, you have to use an int and set it as an identity field. It'll also be fatser.

TPM

 

[zoolandez]

Hi Guys, thanks for the tips. But it didn't worked for me. I am a beginner and used these commands to insert user input from textbox to MS Access database. Whenever the submit button is clicked, the database Autonumber increases but the "Name" field is blank. Really need help guys. Thanks in advance.



Me.OleDbInsertCommand2.Parameters.Add(New System.Data.OleDb.OleDbParameter("Name", System.Data.OleDb.OleDbType.VarWChar, 50, "Name"))

Me.OleDbInsertCommand2.CommandText = "INSERT INTO Name (Name) VALUES ('" & InputName.Text & "')" <<<<<<<<< declaration of the INSERT command

Me.OleDbInsertCommand2.Connection = Me.OleDbConnection1

.
.
.
.
Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
Try

OleDbConnection1.Open()

OleDbInsertCommand2.ExecuteNonQuery() <<<<<<command execution

Catch

Finally

OleDbConnection1.Close()

End Try

End Sub