Jump to content
  • Advertisement
Sign in to follow this  
QuintKillsSharks

[.net] SQL and VB.Net

This topic is 4796 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

How can I use variables in my SQL statements with vb.net?
Dim userparam As String = textbox.text
Dim connect As New OleDbConnection(connectionString)
Dim commandString As String = "SELECT * FROM table WHERE column = :userparam"
Dim dataAdapter As New OleDbDataAdapter(commandString, connect)
This isn't working for me any help would be great thanks.

Share this post


Link to post
Share on other sites
Advertisement

I always put string between ' '. So maybe:

Dim commandString As String = "SELECT * FROM table WHERE column = ':userparam'"

Or maybe your connection is wrong. What kind of error do you get? Syntax error?

Edo

Share this post


Link to post
Share on other sites
when I use ':userparam' I get null values the error occurs when I dont use the single quotes. When I hardcode the userparam like

Select * ...Where column = 'Tom'

it works fine.

edit: error message = No value given for one or more required parameters.

Share this post


Link to post
Share on other sites
Be careful using the 'string manipulation' way. SQL Injection is just around the corner!

Cheers

Share this post


Link to post
Share on other sites
As said before, you probably should avoid using this if this due to security risks, but anyway, this is what you probably want:

Dim userparam As String = textbox.text
Dim connect As New OleDbConnection(connectionString)
Dim commandString As String = "SELECT * FROM table WHERE column = '" & userparam & "'"
Dim dataAdapter As New OleDbDataAdapter(commandString, connect)

This will concatenate the select string with your userparam string and produce what hopefully is a valid connection string.

Again, this is very dangerous to put out there - a clever guy could manipulate the statement to do just about anything with your database.

Share this post


Link to post
Share on other sites
Please be smart and use something like this:


Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM table WHERE userparam= @userparam", connect)
cmd.Parameters.Add("@MyValue", OleDbType.VarChar).Value = userparam

Dim dataAdapter As New OleDbDataAdapter(cmd)


Please, ALWAYS use parameters and make the world a bit safer :)

Regards,
Andre

Share this post


Link to post
Share on other sites
Always use parameterized queries when dealing with databases. Always. IIRC, the names of the parameters are ignored, so you should call query.Parameters.Add() in the order that you specify the parameters in the query string.

I'd recommend just using '?'s in place of parameter names in the query string to avoid confusion. Your query would look like this:
Dim commandString As String = "SELECT * FROM table WHERE column = ?"

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!