none
ASP.net 2005 - Need correct syntax for Insert Values. I am executing the code from a form RRS feed

  • Question

  • I have an ACCESS Database:  LOTTO_db.mdb,  Table:  Lottery

     

    Dim datLotteryDate As Date

    Dim intNum1 As Integer

    Dim intNum2 As Integer

    Dim intNum3 As Integer

    Dim intNum4 As Integer

    Dim intNum5 As Integer

    Dim intNum6 As Integer

     

    Dim conLOTTO As Data.OleDb.OleDbConnection

    Dim strInsert As String

    Dim cmdInsert As Data.OleDb.OleDbCommand

     

    conLOTTO = New Data.OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA   Source= C:\VisualBasic\WebSite3\APP_Data\LOTTO_db.mdb")

    strInsert = "Insert into Lottery ( DrawingDate, Number1, Number2, Number3, Number4, Number5, Number6) values (datLotteryDate, intNum1, intNum2, intNum3, intNum4, intNum5, intNum6)"

    cmdInsert = New Data.OleDb.OleDbCommand(strInsert, conLOTTO)

    conLOTTO.Open()

    cmdInsert.ExecuteNonQuery()

    conLOTTO.Close()

    lblStatus.Text = "Record has been added successfully"

     

    What is the correct syntax for each of the values,  so that the Insert would work?

    Sunday, September 23, 2007 3:43 AM

Answers

  • Syntax should be as follows:

     

    Dim dateParam as new OleDbParameter("@p1", OleDbType.Date)

    dateParam.Value = datLotteryDate

    cmdInsert.Parameters.Add(dateParam)

     

    'repeat the same process for the rest of the parameters, for your intNum1, intNum2 etc... etc... then finally modify the SQL to this:

     

     

    strInsert = "Insert into Lottery ( DrawingDate, Number1, Number2, Number3, Number4, Number5, Number6) values (?, ?, ?, ?, ?, ?, ?)"

     

     

    and that's it! You need to give parameters so then OleDb/database knows its expecting parameters as values and those are then added into the OleDbCommand object's parameters collection.

     

    Remember though, the parameters need to be added in the correct position/order

    Sunday, September 23, 2007 9:54 AM
  • Note that since this is a Microsoft Access database you may need to use

    System.Data.OleDb.OleDbType.DBDate intead of

    System.Data.OleDb.OleDbType.Date for a date parameter.

     

    Don't know what type of numbers you are using but you can probably use

    System.Data.OleDb.OleDbType.Integer for the numeric parameters.

     

    Monday, September 24, 2007 12:29 PM

All replies

  • Syntax should be as follows:

     

    Dim dateParam as new OleDbParameter("@p1", OleDbType.Date)

    dateParam.Value = datLotteryDate

    cmdInsert.Parameters.Add(dateParam)

     

    'repeat the same process for the rest of the parameters, for your intNum1, intNum2 etc... etc... then finally modify the SQL to this:

     

     

    strInsert = "Insert into Lottery ( DrawingDate, Number1, Number2, Number3, Number4, Number5, Number6) values (?, ?, ?, ?, ?, ?, ?)"

     

     

    and that's it! You need to give parameters so then OleDb/database knows its expecting parameters as values and those are then added into the OleDbCommand object's parameters collection.

     

    Remember though, the parameters need to be added in the correct position/order

    Sunday, September 23, 2007 9:54 AM
  • Note that since this is a Microsoft Access database you may need to use

    System.Data.OleDb.OleDbType.DBDate intead of

    System.Data.OleDb.OleDbType.Date for a date parameter.

     

    Don't know what type of numbers you are using but you can probably use

    System.Data.OleDb.OleDbType.Integer for the numeric parameters.

     

    Monday, September 24, 2007 12:29 PM