Storing Value from SQL Queries

Hi

The current project I'm working on involves me to retrive and generate new ID from my SQL tables. I have already worked out an algorithm but is having problem with the implementation.

The algorithm involve me to have a custom query to retrieve the highest ID in the table and then storing it as a variable (int) in VB Express. Then using that variable, I can generate new ones by incrementing it. My question is, how do I store data from SQL into a variable

The query looks something like this:
SELECT MAX(ProductID) FROM Products

Thanks

Answer this question

Storing Value from SQL Queries

Sumardi

HI,

anyway by retrieving the max value and increment it manualy it could cause to you trouble anyway you can use this code, it retrieve the max customer id from your dataset

Private Sub GetTheMaxValue()

Dim dv As DataView = New DataView(Me.Db1DataSet.CustomerTable, "CustomerID = Max(CustomerID)", "", DataViewRowState.CurrentRows)

Dim drv As DataRowView

For Each drv In dv

'which allready just contain one row

MessageBox.Show(drv("CustomerID"))

Next

End Sub

hope that helps
sandeeppatwardhan

Hi,
I am afraid that above my suggested method is wrong. if you get any error you can place the error in the post too and the next thing is for above my suggested methodology first of all you have 2 create database connection and and then conly execute and get the recordset back with values..

hope 2 u will solve the problem else let's post the error that arised..
MarkR9999
This is not the best way to get what you want, but to do it as you are you would create an SqlCommand object and call ExecuteScalar. You would have to cast the return value as an Integer because it will be returned as an Object reference:

Dim maxID as Integer = CInt(myCommand.ExecuteScalar())
Alex Franke

hi...

I am not the expert one but i use to do it first by opening a recordset and then getting value from it(VB 6.0). Hope the logic is same.

Like

****************************
Dim rsT as recordset
dim Ret as Integer

Set rst=new Recordset
rst.open "Select max(id) from Products",Conn

Ret=iif(isnull(rst.fields(0)),0,rst.fields(0))

*****************************************
Bert Loedeman

hi,

if you have the id field as premary key in your database you don't need to retrieve or change anything when you gonna add a new record the id value will be created for you "just intentionally forget this field in your code" , you can use it to sort , filter , delete , update records but forget it in insert record if its a primary key field and have a unique value

you can try it open your table in dataview and try to add new record manualy without enter any value in your id field and you will find the id auto generated for you

hope it helps
Randy Hoogerhyde
Two errors that arrised are listed below.

1. Type RecordSet is not defined
2. Name 'isNull' is not declared

Is there a namespace that I should be using
ToyImp
Over the past couple of days, I've been experimenting with a few possible solutions. Unfortunately nothing has work thus far. I've tried to leave the primary key id field empty, but if that happens, the Update would not execute cause the database can't take a null for the field. I also tried to retrieve it like Kebians said, but that hasn't worked out either. Perhaps I'm just not interpreting the solutions correctly
Storing Value from SQL Queries

Answer this question