Passing Variables through INSERT

[Ravon]

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

 

[Ravon]

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.ProjectOrgDataSet)
    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