Link to home
Create AccountLog in
Avatar of b001
b001Flag for Afghanistan

asked on

locate record has same value as a variable using Visual Studio .net

Hi Experts

I have a very large PRODUCT table and is indexed on Column ProdID. I would like to find the first record that maches the value stored in veriable ICODEX. Once found I would like to store PRODUCT tables field value (ProdIdesc to ProdIdescx and ProdPrice to Prodpricex).
Thanks
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

You can find the product matching the id with below code

Dim dbadp as new SQLDataAdapter("Select * from Product Where ProdID=" & iCODEX, "connectionstring")

Dim dTable As New DataTable
dbadp.Fill(dTable)
dbadp.Dispose

If dTable.Rows.Count > 0 Then
   msgbox "Product Exists!"
End If
Avatar of b001

ASKER

Hi CodeCruiser:
I have tried the following code :

1:      Dim con As SqlConnection = New SqlConnection("Data Source=BILLHOME\SQLEXPRESS;Initial Catalog=FMS;Integrated Security=True")
2:      con.Open()
3:      Dim icodex As String = "P100"
4:      Dim dbadp As New SqlDataAdapter("Select * from Product Where ProdID=" & icodex, "con")
5:      Dim dTable As New DataTable
6:      dbadp.Fill(dTable)
7:      If dTable.Rows.Count > 0 Then
8:          MsgBox("Product Exists!")
9:      End If

and at line 4 I get the follwing error


System.ArgumentException was unhandled
  Message="Format of the initialization string does not conform to specification starting at index 0."
  Source="System.Data"
  StackTrace:

Please help

Thanks
 
This
 Dim dbadp As New SqlDataAdapter("Select * from Product Where ProdID=" & icodex, "con")

needs to be
 Dim dbadp As New SqlDataAdapter("Select * from Product Where ProdID=" & icodex, con)
Avatar of b001

ASKER

Thnaks CodeCruiser:

Now I have following error on line 6

Invalid column name 'P100'.
What is the type of ProdID? Is it int or varchar?
Avatar of b001

ASKER

Hi CodeCruiser
 ProdID field is Varchar(20) and the table is indexed on this field as well.
Thanks
If ProdID is varchar then

 Dim dbadp As New SqlDataAdapter("Select * from Product Where ProdID='" & icodex & "'", con)
Avatar of b001

ASKER

HI CodeCruiser:
That works.
one more help
Please expalin How to store the information from Dtable fields to Veriables or TextBox1,txt  so that I can display the information on my form.
displaying ProdID  in TextBox1.txt
                 Icode    in TextBox2.txt
and so on,

Thanks very much.
This code will show the values from first row

Textbox1.Text = dTable.Rows(0).Item("ProdID")
Textbox2.Text = dTable.Rows(0).Item("lcode")
Avatar of b001

ASKER

Hi CodeCruiser
Many thanks

If ProdID is varchar then the code is
 Dim dbadp As New SqlDataAdapter("Select * from Product Where ProdID='" & icodex & "'", con)

that works file.
What should the code  be if ProdID is Int ?
Thanks
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account