SQL statement not working

[needhelpso]

I have a dropdownbox that I select a number. I assign the value of the box to x. I then want to use x in an sql statement.

Here is the code for the button:

Sub Button1_Click(sender As Object, e As EventArgs)
Dim ds As DataSet =new DataSet()
dim x as string

x=dropdownlist1.selecteditem.value

mylabel.text=x
Dim MyCommand2 as new OleDbDataAdapter("select * from timesheet where PayPeriod=x", MyConn)


MyCommand2.Fill(ds, "payperiod&quot

Myrepeater.DataSource = ds.Tables("payperiod&quot.DefaultView

databind()
End Sub


x will be equal to whatever I select in the drop down box (I verified that). However, this code works if I put in a number directly into:

Dim MyCommand2 as new OleDbDataAdapter("select * from timesheet where PayPeriod=12", MyConn)

However, if I enter x which contains the value of 12 like this:

Dim MyCommand2 as new OleDbDataAdapter("select * from timesheet where PayPeriod=x", MyConn)

It errors out even though the value is 12.

Any ideas?

 

[StylizIT]

hi m8, you have to put x like this:

Dim MyCommand2 as new OleDbDataAdapter("select * from timesheet where PayPeriod=' "& x &" '", MyConn)

hope it helps


You can better regret that you have done something than regret the fact you done nothing

 

[needhelpso]

Thanks for the suggestion but I am still receiving datatype mismatch when I use that code. Any other ideas?

 

[StylizIT]

you have to declare x as an integer m8 if you want to use it as a number.

You can better regret that you have done something than regret the fact you done nothing

 

[Isadore]

First guess, sty's solution should have been adequate, since that is standard syntax for a string - you might try, e.g.,

select * from timesheet where PayPeriod=" & x

 

[Isadore]

sty -

might have been obvious but your string:

PayPeriod=' "& x &" '",

may also be written as:

PayPeriod='" & x & "'",

..assuming x is a string

 

[needhelpso]

Thanks for trying to help. Here is the error; I have X as an integer.

Data type mismatch in criteria expression.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Source Error:


Line 49: Dim MyCommand2 as new OleDbDataAdapter("select * from timesheet where PayPeriod=' "& x &" '", MyConn)
Line 50:
Line 51: MyCommand2.Fill(ds, "payperiod&quot
Line 52:
Line 53: Myrepeater.DataSource = ds.Tables("payperiod&quot.DefaultView


Source File: D:\My Class Stuff\Languages of the web\Project3\sqltest.aspx Line: 51

 

[StylizIT]

when you created the table, did you specified the payperiod colmun as an integer or something else?

You can better regret that you have done something than regret the fact you done nothing

 

[Isadore]

so sty was right on point...glad you found your error.

 

[needhelpso]

The column payperiod is a long datatype in the table. I tried making X a long but received the same error. It is really odd because this works:

Dim MyCommand2 as new OleDbDataAdapter("select * from timesheet where PayPeriod=12", MyConn)

The code above will bring back and display all payperiods equal to 12. But if x=12 and I put X in there I receive the type mismatch even though X is a long variable type.

 

[StylizIT]

see if declaring x as long and using this:

x = dropdownlist1.selecteditem.text

instead of this

x = dropdownlist1.selecteditem.value

works, otherwise I think

dropdownlist1.selecteditem.value as to be converted to a long when passing it to x

You can better regret that you have done something than regret the fact you done nothing

 

[needhelpso]

Even if I declare X as an integer and make x=12 I get type mismatch errors. But if I replace X in the SQL with 12 it is fine. However if I put payperiod='"&12&"'", I get an error. If I put payperiod=12 it is ok.

Sub Button1_Click(sender As Object, e As EventArgs)
Dim ds As DataSet =new DataSet()
dim x as integer

'x=dropdownlist1.selecteditem.text
x=12
mylabel.text=x
Dim MyCommand2 as new OleDbDataAdapter("select * from timesheet where PayPeriod='" & x & "'", MyConn)

MyCommand2.Fill(ds, "payperiod&quot

Myrepeater.DataSource = ds.Tables("payperiod&quot.DefaultView

databind()


End Sub

 

[StylizIT]

m8 do not get confuced about the "& x &". The reason you get an error when you do this "& 12 &" is because 12 is not a variable but a number. You use this "& variable &" only when you want the value of an object
( your x variable) to be part of a string for example.

But m8 try redesigning your databse and set the column type of payperiod to integer and then declare x as integer.I think there might be a difference between a database long type and a .NET long type. << (not shure about that though).



You can better regret that you have done something than regret the fact you done nothing

 

[needhelpso]

Tried redesigning the database column payperiod and made it an integer. Made x an integer but still received datatype mismatch...no luck still (

 

[needhelpso]

Dim MyCommand2 as new OleDbDataAdapter(&quot;select * from timesheet where PayPeriod=&quot; & x & &quot;&quot;, MyConn)

Hi all, I figured it out! Just to let you all know the problem was that I had an extra quote around the value X. I did not need this as the value of X was an integer!

5 hours...gotta love programming! Thanks for all your help!

 

[StylizIT]

hehe glad you found your problem m8, sometimes it can be something so small, but it takes a lot of time to figure out what the problem is

You can better regret that you have done something than regret the fact you done nothing