Hello, I'm new to using databases and 'im stuck on pushing values into a new database.

I've created all the database tables in 1 project, using MSACCESS 2007. I'm using VB2008 to connect and organize content.

While debugging I can see that i am getting a connection to access, however i never get notice of targeting the 'Main' table which is where I want the information to go. In the end I get syntax errors with the SQL statement. I believe it's with the .addparameters

Any help or alternate suggestions is greatly appreciated, ty



Dim Projnum As Integer = 0
Dim Description As String = ""
Dim Revision As Integer = 0
Dim Company As String = ""
Dim Stats As Boolean = False
Dim Notes As String = ""
Dim FGQ As New Queue

''''in here ^^ variables are defined,
then passed below''''

If VBAccess.InsertMainRow2(Projnum, Description, _
Revision, Company, Stats, Notes) = False Then
MsgBox("records didn't take")
End If
more stuff~~ to end.the above is all the code I use to open our datadump.txt files with all the information. I sort it and apply to variables, passing through the function to an alternate class sheet.. VBAccessCon



Option Explicit On
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Access
Imports dao
Imports Microsoft.Office
public class VBAccessCon

Public Shared Sub HandleConnection( _
ByVal conn As OleDbConnection)
''''' handles state of connection, on or off
With conn
Select Case .State
Case ConnectionState.Open
.Close()
.Open()
Exit Select
Case ConnectionState.Closed
.Open()
Exit Select
Case Else
.Close()
.Open()
Exit Select
End Select
End With
End Sub

Public Shared Function InsertMainRow(_
ByVal Projnum As Integer,_
ByVal Description As String,_
ByVal Revision As Integer,_
ByVal Company As String,_
ByVal Active As Boolean,_
ByVal Note As String) As Boolean
Dim cnInsert As New _
OleDbConnection(_
"Provider=Microsoft.ACE.OLEDB.12.0;_
Data Source=C:\Users\Ravon_
\Documents\ProjectOrg.accdb")

Dim cmdInsert As New OleDbCommand

Dim query As String = "INSERT INTO 'Main' _
VALUES(" & Projnum & ",""" & Description_
& """," & Revision & ",""" & Company & """,_
" & Active & ",""" & Note & """)"
Dim iSqlStatus As Integer
cmdInsert.Parameters.Clear()
Try
With cmdInsert
.CommandText = query
.CommandType = CommandType.Text
'Now add the parameters to our query
'NOTE: Replace @value1.... with your
'parameter names in your query
'and add all your parameters in this fashion
.Parameters.AddWithValue("Proj#", Projnum)
.Parameters.AddWithValue("Description", Description)
.Parameters.AddWithValue("Revision", Revision)
.Parameters.AddWithValue("CompanyName", Company)
.Parameters.AddWithValue("Status", Active)
.Parameters.AddWithValue("Notes", Note)
.Connection = cnInsert
End With
HandleConnection(cnInsert)
iSqlStatus = cmdInsert.ExecuteNonQuery
If Not iSqlStatus = 0 Then
Return False
Else
Return True
End If
Catch ex As Exception
MsgBox(ex.Message)
Finally
HandleConnection(cnInsert)
End Try
End Function
`more stuff
end class


**edit i tried another version with DAO connections but after some searching I found "Visual Basic 2008 does not support DAO and RDO data binding to controls, data controls, or RDO User connection" straight from their site. So tomorrow ill be trying the ADO connection if nothing else :D

Hi hello 2nd post..

I've found a way to insert data into the database and it was actually really easy. It took forever to realize that the drag and drops were only in the form design mode and not into code :/

Anyway it brings up a question.. I dont understand why people use the oleDB connection method and SQL commands if all you have to do in 2008VB w/ 2007Access is drag the tableadapter and databindings into the Design Form?
Is an oledb connection faster? more reliable? more versatile?

this is the code after the drag and drop

(some parts are just comments on functionality, i left out the code)

Public Class Form1
Private Sub MainBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MainBindingNavigatorSaveItem.Click
Me.Validate()
Me.MainBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.ProjectOrgData Set)
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.MainTableAdapter.Fill(Me.ProjectOrgDataSet.Main )
ReadData()
MsgBox("data read")
UploadData()
End Sub
Private Sub ReadData()
''reads lines of .txt into a Queue
''splits lines into values and categorys
End Sub
Private Sub UploadData()
'' values defined up here
'' do any last error checking on numbers
'' then insert into DB
MainTableAdapter.Insert(Projnum, Descriptions, revision, Companys, Stats, Notes)
End Sub
Private Sub RefreshData()
Me.MainTableAdapter.Fill(Me.ProjectOrgDataSet.Main )
End Sub
End Class