CODEWITHSUNDEEP
sqlvb.net
Luis
Luis

Reputation: 1

Vb.Net 2010 - SQL Insert Command with autonumeric value

I'm looking to add a row with an autonumeric value using SQL.

I'm using Studio VB 2010. I have a very simple table with 2 fields: ID Autonumeric Model Text field. 

    constr = "INSERT INTO procedures VALUES('" & txtFName.Text & "')"
    cmd = New OleDbCommand(constr, cn)

    cn.Open()
    Me.i = cmd.ExecuteNonQuery

A message says a parameter is missing, so my question is... How can I add in the SQL command this automatic value? (ID)

Should I get the last ID number and +1 ?? I think there's gotta be a simple way to do it.

Thank you.

Update #1 I am now trying parameterized queries as suggested... I found this example, 

  Dim cmdText As String = "INSERT INTO procedures VALUES (?,?)"
    Dim cmd As OleDbCommand = New OleDbCommand(cmdText, con)
    cmd.CommandType = CommandType.Text
    With cmd.Parameters
        .Add("@a1", OleDbType.Integer).Value = 0
        .Add("@a2", OleDbType.VarChar).Value = txtFName.Text
    End With
    cmd.ExecuteNonQuery()
    con.Close()

But still, I'm geting a Syntaxis error. Any thoughts?

Thanks to you all.

UPDATE #2 This code seems to work if I give the next ID number, but again, how can I do it automatically?

Dim cmdText As String = "INSERT INTO procedures VALUES (?,?)"
Dim cmd As OleDbCommand = New OleDbCommand(cmdText, con)
cmd.CommandType = CommandType.Text
    With cmd.Parameters
        .AddWithValue("@a1", OleDbType.Integer).Value = 3
        .AddWithValue("@a2", OleDbType.VarChar).Value = txtFName.Text
    End With
    cmd.ExecuteNonQuery()
    con.Close()

Any comments? Thanks again.

UPDATE #3 This Code gives me Syntaxis Error I just put my only one column to update, the second one is the autonumber column, as I was told to try. 

 Dim Con As OleDbConnection = New OleDbConnection(dbProvider & dbSource)
 Dim SQL_command As String = "INSERT INTO procedures (procedure) VALUES ('Model')"
Dim cmd As OleDbCommand = New OleDbCommand(SQL_command, Con)

    Try
        Con.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        Throw ex
    Finally
        Con.Close()
        Con.Dispose()
    End Try

UPDATE #4 - SOLUTION

I'm putting this code in here in case someone finds it useful. 

    dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;"
    dbSource = "Data Source = c:\gastrica\dabase.accdb"
    Con.ConnectionString = dbProvider & dbSource


Dim Con As OleDbConnection = New OleDbConnection(dbProvider & dbSource)
    Dim SQL_command As String = "INSERT INTO [procedures] ([procedure]) VALUES (?)"
    Dim cmd As OleDbCommand = New OleDbCommand(SQL_command, Con)
    cmd.CommandType = CommandType.Text
    With cmd.Parameters
        .AddWithValue("@procedure", OleDbType.VarChar).Value = txtFName.Text
    End With

    Try
        Con.Open()
        cmd.ExecuteNonQuery()
        Dim varProcedure As String = cmd.Parameters("@procedure").Value.ToString()
        MessageBox.Show("Record inserted successfully. Procedure = " & varProcedure)
    Catch ex As Exception
        Throw ex
    Finally
        Con.Close()
    End Try

Thanks all for your comments and help.

Upvotes: 0

Views: 353

Answers (3)

Luis
Luis

Reputation: 1

Thanks for your answers.

I couldn´t do it, it gives me errors. So I end up with a single variable reaching the next Auto Value. Using a simple SQL command. And then, everything runs good. 

 vNextAuto = GetDB_IntValue("SELECT TOP 1 * FROM procedures ORDER BY ID DESC", "ID") + 1

    Dim SQL_command As String = "INSERT INTO procedures VALUES (?,?)"
    Dim cmd As OleDbCommand = New OleDbCommand(SQL_command, Con)
    cmd.CommandType = CommandType.Text
    With cmd.Parameters
        .AddWithValue("@id", OleDbType.Integer).Value = vNextAuto
        .AddWithValue("@a2", OleDbType.VarChar).Value = txtFName.Text
    End With

    Try
        Con.Open()
        cmd.ExecuteNonQuery()
        'Dim id As String = cmd.Parameters("@id").Value.ToString()
        'MessageBox.Show("Record inserted successfully. ID = " & id)
    Catch ex As Exception
        Throw ex
    Finally
        Con.Close()
        Con.Dispose()
    End Try

Upvotes: 0

Eduardo Espinosa
Eduardo Espinosa

Reputation: 86

Try this one:

constr = "INSERT INTO procedures (ID, Model) VALUES (0,'" & txtFName.Text & "')"
'or (not sure)
constr = "INSERT INTO procedures (Model) VALUES ('" & txtFName.Text & "')"

When use 0 as value, in autonumeric fields SQL insert the next number

Upvotes: 0

travgm
travgm

Reputation: 131

You need to specify the columns:

INSERT INTO procedures (columnname, columnname2) VALUES ('test', 'test');

Here is a sql fiddle showing an example:

http://sqlfiddle.com/#!9/3cf706/1

Upvotes: 1

Related Questions