Storing Value from SQL Queries

Storing Value from SQL Queries

• Dan Vallejo - RampGroup

  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

  
  

• Asim Zeeshan

  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

  
  
  

• ShubertAtEastridge

  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
  

• Joel McCormick

  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))

  *****************************************

  
  
  

• PoKemonInstructor

  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

  
  
  

• Michiel1978

  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..

  
  
  

• JoelBarish

  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())
  
  
• Storing Value from SQL Queries