Problem with type of data in SQL query

[christiancillo]

Hello everyone, i'm new here but i've been having a problem lately with a sql query inside the code i'm writing, and i don't know what else to do. I've been trying to retrieve data from a table, and i want to use a variable for the "where" field in the SQL query, but it keeps saying that the data are not the same type. Here it is:

Dim cmdimp As New OleDb.OleDbCommand("select * from importer where cod_importer='" & codimporter & "'", conn)

The point is, i need it to be an integer because the primary key is the same, but i can't figure out how to specify it. I hope i posted this on the aproppiate place, and if not, i apologize, but i didn't have much time to go through it. All help will be appreciated. Thank you all.

[lolafuertes]

I'll assume that codimporter is of type integer.

If this is true, then

Dim cmdimp As New OleDb.OleDbCommand("select * from importer where cod_importer = " & codimporter.ToString & ";", conn)

Else

Dim intCodeImporter as Integer = -1
Try
    intCodeImporter = Ctype(codimporter, Integer)
Catch ex as Exception
    msgbox "This is not an interger: " & codimporter
    '
    '  take any action when this is not an integer
    '
End try
Dim cmdimp As New OleDb.OleDbCommand("select * from importer where cod_importer = " & intCodeImporter.ToString & ";", conn)

Hope this helps

[christiancillo]

Thanks for responding so fast, but unfortunately i couldn't fix my problem. You were right, my variable is an integer, and i need to include it in the sql query, but it takes it as a string. The first option you gave me didn't show any errors, but would't retrieve the data.

I'm so stuck :(

[lolafuertes]

A few questions:
What is the contents (value) of codimporter?
Exists some data in the DB where the cod_importer is equals to the value passed?
Do you get some error?
After the Dim cmdimp As New OleDb.OleDbCommand("select * from importer where cod_importer = " & codimporter.ToString & ";", conn), what are the next sentences you use to retrieve the data?

[christiancillo]

Thanks again for responding so quickly!

In this case, i retrieved the value from another query on the database, also checked if it really is inside the variable, and it is, showed up in a msgbox.

I also tried to see if it was the query that wasn't correct, so instead of trying to put a variable, i just wrote 1, as it is inside the DB, and it retrieved the data perfectly.

When I used your first solution, i didn't get any error, but it didn't retrieve the data either.

I'll show you the code so you can see.

Dim cmdimp As New OleDb.OleDbCommand("select * from importer where cod_importer = " & codimporter & ";", conn)
Dim drimp As OleDb.OleDbDataReader
conn.Open()

drimp = cmdimp.ExecuteReader
                    While (drimp.Read)
                        ss.ComboBox4.Items.Add(drimp("Cod_importer") & " - " & drimp("Name_importer"))
                    End While
                    drimp.Close()

thanks again, i really appreciate

[lolafuertes]

Please try the following:

Dim cmdimp As New OleDb.OleDbCommand("select cod_importer, Name_Importer from importer where cod_importer = " & codimporter & ";", conn)Dim drimp As OleDb.OleDbDataReaderconn.Open() drimp = cmdimp.ExecuteReader                    While (drimp.Read)                        ss.ComboBox4.Items.Add(drimp("Cod_importer") & " - " & drimp("Name_importer"))                    End While                    drimp.Close()Dim cmdimp As New OleDb.OleDbCommand("select * from importer where cod_importer = " & codimporter & ";", conn)
Dim drimp As OleDb.OleDbDataReader
conn.Open()

drimp = cmdimp.ExecuteReader
                    While (drimp.Read)
                        ss.ComboBox4.Items.Add(drimp.GetInt32(0).ToString & " - " & drimp.GetString(1))
                    End While
                    drimp.Close()

A few hints:
1) When executing a query agains a DB, request only the fields you need
2) The drimp("") refers to a column. You need to convert the object to a column, then get the value depending on the type of the column. Using the Getxxxx functions of the reader you can achieve the same results easely.
3) To get ride of the nulls in the DB, you can use the IsDbNull function to determine it.

Hope this helps

[christiancillo]

Thanks again for your time, Lola, but it still won't work. The data won't be retrieved, the datareader comes out empty. I also tried to concatenate the variable as if i was doing the query without it, but it says that i have a syntax error... don't know what else to do.

Does it have anything to do with it that i have set the value of the field i'm trying to retrieve (cod_importer) as autoincrementable? I don't know the word for that in English, i'm sorry.

Anyways, i'm gonna go to bed, i'll try tomorrow morning again, and i'll keep you posted! Thanks :)

[lolafuertes]

Can you post a few rows of importer table?

TIA