Select, Insert, Update With Parameters using Oledb

Hi EE,

What is the best way to select, insert and update data in VB 2008?  
It seems tedious to write out all the values of an insert statement and I read somewhere that you could use parameters.
Can you use parameters with a MS Access backend?  
If yes, could you provide an examples?  I especially need an example of the insert statement that uses parameters and returns the "SELECT @@IDENTITY"?
Thank you
SheritlwAsked:
Who is Participating?
 
ITHelper80Connect With a Mentor Commented:
Here is how I capture the @@Identity
        myPath = Server.MapPath("yourdb.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
 
        Dim insCmd As New System.Data.OleDb.OleDbCommand
 
        insCmd.CommandText = "insert into Data (your code) values (your code)
 
        insCmd.Connection = con
 
        Dim idCmd As New System.Data.OleDb.OleDbCommand
        'Captures AutoID Number
        idCmd.CommandText = "select @@IDENTITY"
 
        idCmd.Connection = con
        con.Open()
        insCmd.ExecuteNonQuery()
        ' sets newid as value of @@Identity
        Dim newid As Integer = idCmd.ExecuteScalar()
        con.Close()

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Here you have some examples:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.aspx
Select on the Remark the ExecuteReader, ExecuteNonQuery  and ExecuteScalar methods to see other examples.

' Use this and a ExecuteScalar method
Dim SQL As String = "INSERT INTO myTable([username]) VALUES (@username); Select @@IDENTITY;"

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
@ITHelper80,
You don't need to do two calls to the database. You can use only one ExecuteScalar() that inserts and gets the inserted ID.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SheritlwAuthor Commented:
Hi ipaulino,
Do you have an example that you can provide here?  Everytime I click on a link my IE crashes.
Thanks,
0
 
ITHelper80Commented:
Honeslty I am still learning this myself but I thought

Execute NonQuery..
1.It will not return any data.
2.It is used with insert and update.
3.It returns only the number of rows affected.

Execute Scaler..
1.It returns only one value.
2.That value will the first column first row value.

How can you perform an Insert/Update etc with the Execute Scaler command?
0
 
SheritlwAuthor Commented:
Sorry I guess this question was for jpaulino.

Can you provide an example on using the ExecuteScaler command?

Thanks
0
 
ITHelper80Commented:
My code shows you how to use the ExecuteScaler command to retrieve the @@Identity value.

The link JPaulino posted even said to use ExecuteNonQuery for Selects,Inserts and Updates so I am confused why he says not to use it.
0
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
>> Honeslty I am still learning this myself but I thought
So do I ... always learning.
 
You code is right, but you can do all in one command. ExecuteScaler() also executes an T-SQL command and if you saw my first post you will see that I have used:
Dim SQL As String = "INSERT INTO myTable([username]) VALUES (@username); Select @@IDENTITY;"

You can do all like this:

            Using connection As New OleDbConnection(connString)
                Dim SQL As String = "INSERT INTO myTable([username]) VALUES (@username); Select @@IDENTITY;"
                Using command As New OleDbCommand(SQL, connection)
 
                    Dim result As Integer = command.ExecuteScalar
                    Debug.WriteLine(result.ToString)
 
                End Using
            End Using

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You should use ExecuteNonQuery if you don't want need to retrieve any values from the T-SQL. The ExecuteNonQuery also returns the number of affected record.
If you do "INSERT INSERT INTO myTable([username]) VALUES (@username)"  the result should be 1
 

            Using connection As New OleDbConnection(connString)
                Dim SQL As String = "INSERT INTO myTable([username]) VALUES (@username);"
                Using command As New OleDbCommand(SQL, connection)
 
                    Dim result As Integer = command.ExecuteNonQuery 
                    Debug.WriteLine(result.ToString)
 
                End Using
            End Using

Open in new window

0
 
SheritlwAuthor Commented:
Hi iPaulino,

Sorry I couldn't read your post... everytime I click on a link my IE crashes.
I will try this now.
Thanks
0
 
SheritlwAuthor Commented:
Hi Paulino,

I tried the example you provided with the Select @@Identify at the end and got an error saying there were characters at the end of insert statement.
Here is the insert I used
str = "Insert into Company " _
       & " (CompanyName, Address1, Address2, CityName, StateID, Postal, " _
       & " CntryID, DefaultStateID, DefaultCntryID) Values " _
       & " ('" & msCompanyName & "', '" & msAdd1 & "','" & msAdd2 & "', " _
       & " '" & msCity & "'," & miStateID & ",'" & msPostal & "', " _
       & " " & miCntryID & ", " & miDefStateID & ", " & miDefCntryID & "); " _
       & " Select @@IDENTITY;"
0
 
SheritlwAuthor Commented:
This is what I finally got to work.

            Using cmd As New OleDbCommand(GenerateSQLInsert(), oleCnn)
                Dim idCmd As New System.Data.OleDb.OleDbCommand
                oleCnn.Open()
                i = cmd.ExecuteNonQuery()
                idCmd.CommandText = "Select @@IDENTITY"
                idCmd.Connection = oleCnn
                i = idCmd.ExecuteScalar()
                oleCnn.Close()
            End Using

            Return i

I will split the points
Thanks for your help
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
>> I tried the example you provided with the Select @@Identify at the end and got an error saying there were characters at the end of insert statement.
What code have you done for this ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.