asked on

OLEDB SQL Insert to Access DB

hi all - hope you can help me!

i am trying to do a simple OLEDB SQL insert to an Access DB on my local C drive but i am having some probs!

I am inserting a mixture of strings, integers, dates and currency.
I know some values need a ' character before and after, some do not need anything, and i have heard that datetime values need a # before and after. Please can you advise me what I need to put for these data types...

string
integer
datetime
currency

thank you!

SQL = "INSERT INTO dbo_onair (onairid, itemid, duration, description, presenter, producer, costprice, nowatprice, closingprice, enddate, startdate)"
        SQL = SQL & " VALUES ("
        SQL = SQL & "'" & onairid & "',"
        SQL = SQL & "'" & itemid & "',"
        SQL = SQL & "'" & duration & "',"
        SQL = SQL & "'" & description & "',"
        SQL = SQL & "'" & presenter & "',"
        SQL = SQL & "'" & producer & "',"
        SQL = SQL & "'" & costprice & "',"
        SQL = SQL & "'" & nowatprice & "',"
        SQL = SQL & "'" & closingprice & "',"
        SQL = SQL & "'#" & enddate & "#',"
        SQL = SQL & "'#" & startdate & "#'"
        SQL = SQL & ")"
        'SQL = SQL & " VALUES (onairid,itemid,duration,description,presenter,producer,costprice,nowatprice,closingprice,enddate,startdate)"
        
        conn = New OleDbConnection(connectionstring)
        command = New OleDbCommand
        command.Connection = conn
        command.CommandText = SQL
        command.Connection.Open()
        command.ExecuteNonQuery()
        command.Connection.Close()

Open in new window

Jorge Paulino

You have to read this question http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23754631.html
It explains and shows some examples and advantages of the parameters.

craignorris28

ASKER

sorry i am so new to .net and cant figure out looking at other peoples code!

can you please just tell me what parameters (if any) i need for an sql insert as per my question?

thank you

Jorge Paulino

You just have to follow the bellow example. You define the fields you want to insert (or update) and define a parameter for each one in the right order. You define also the datatype and you don't need to worry about the formats, the #, commas, etc.
Look to the example.

        Try
            Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDb.mdb;"
 
            Dim SQL As String = "INSERT INTO Table1 ([name],[number],abc,def) VALUES (?,?,?,?)"
            Using connection As New OleDbConnection(connString)
 
 
                Dim command As New OleDbCommand(SQL, connection)
                command.Parameters.Add("name", OleDbType.VarChar).Value = "jpaulino"
                command.Parameters.Add("number", OleDbType.Integer).Value = 123
                command.Parameters.Add("abc", OleDbType.VarChar).Value = "O'Brien"
                command.Parameters.Add("def", OleDbType.Date).Value = Date.Today
 
                connection.Open()
                Dim x As Integer = command.ExecuteNonQuery()
                If x <> 1 Then
                    Throw New ArgumentException("It was not possible to write on the DB.")
                End If
                connection.Close()
            End Using
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
 
        End Try

Open in new window

ASKER CERTIFIED SOLUTION

Priest04

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jorge Paulino

Ok, you choose the hard way :)