URGENT. mysql and VB variables

[Chathuri]

Hii,
I need to set a VB variable so that it contains the value of a column in the mysql.
For instance :
my table name is storageinfo which consists of fields part_num, part_name, quantity etc. I want to put in to a variable (declared in VB) the quantity of a selected part_number.
I tried the below coding but a does not take the value of quantity.
THE CODE :

dim a as integer 

cmd.CommandText = "select tablename "& a &" = quantity where part_num = 6058;"

PLEASE HELP!

CS

[Campion]

Try

"SELECT quantity FROM storageinfo WHERE part_num = 6058;"

You may also want to try putting some apostrophes around that 6058 as well.

Also, while I'm at it, are you using a database connector, etc?

[Chathuri]

I have already done the connection and have already displayed the mysql tables on datagridview.

But the thing is i need to insert the quantity details into a text box. How do i do it.
somethin like :

cmd.commandtext = "select quantity from storageinfo where part_num = 6058;"
txtquant.Text = quantity.tostring

If i use this code the quantity is a field in mysql and VB wont recognise it when I want to display it on a textbox right ?

Is there anyway to get only one row from a datatable without using a for loop ?

Thanks.

[zeezee]

Hey Chathuri,
You need to have a Recordset object .try this, I have used ADODB, you can use any method to connect to DB. DAO,ADODB, etc ...

Public Sub setQuantinty(ePartNum As Integer)
    Dim quantityVal As String ' or integer
    Dim rst1 As New ADODB.Recordset
    Dim strsql As String
    
    
    strsql = "SELECT Quantity FROM storageinfo WHERE  part_num = " & ePartNum
    rst1.Open strsql, dbcon ' dbcon is the ADODB connection
    
    If rst1.RecordCount > 0 Then
        rst1.MoveFirst
        txtQuantity.Text = rst1.Fields("Quantity")
        
    Else
        txtQuantity.Text = ""
    End If
    
    rst1.Close
    Set rst1 = Nothing
End Sub

And look into this thread also




PS : R U in SL ?????

[zeezee]

Is there anyway to get only one row from a datatable without using a for loop ?

There is command in MySQL called LIMIT you can limit the recordset to desired value using this. But this needs carefull planing.. That means you have to see the order of the records you get. try using Order By to help this, but I think still its risky.

select quantity from storageinfo where part_num = 6058 LIMIT 1

Best way , as I think , is to use a WHERE clause and carefully planing the DB and your queries.

Cheers !!

[zeezee]

You may also want to try putting some apostrophes around that 6058 as well.

Hey Campion you dont need to put apostrophes for numeric columns. You need it for String, Date, etc ... like columns. Refer to the manual for data types and how to use them.

[Campion]

Hey Campion you dont need to put apostrophes for numeric columns. You need it for String, Date, etc ... like columns. Refer to the manual for data types and how to use them.

They're not needed for mySQL, but mySQL does not reject the SQL string if you DO put them in. However, this does not mean that other database systems don't require them, either.

[zeezee]

They're not needed for mySQL, but mySQL does not reject the SQL string if you DO put them in. However, this does not mean that other database systems don't require them, either.

You are right, but thats why added, "reffer to the manual" also. it differs from DB to DB. further, it could differ from version to version also.
And MySQl doesnt reject even if you add quotes for numeric.
But for other types like String or Date, without it, could add wrong information or reject the query itself.
Always reffer to the DB manual and try the querry before you put it in the program unless your are damn sure about it