Simple SQL connection query

[CookieNZ]

I am very new to programming. Moreover, I only started this morning!

I am trying to query an SQL database to return the delnotenumber field, but I don't seem to be having much joy. Where am I going wong? I am not sure if the cmdstring is correct, nor if I am binding the results correctly to the deliverynumber label.

Dim DelID AsString = Request.QueryString("sdeliveryID")

Dim connstring AsString = "server=(local);database=TharData;uid=sa;pwd=tharSQL"
Dim conn AsNew SqlConnection(connstring)
Dim cmdstring AsString = "Select delnotenumber from deliverynote where ID" & "'DelID'"
Dim cmd AsNew SqlCommand(cmdstring, conn)

conn.Open()

Dim reader As SqlDataReader = cmd.ExecuteReader
DeliveryNumber.Text = reader.ToString

 

[TechGnome]

The WHERE clause of your SQL doesn't make sense.

What is it you are trying to do?

-tg

 

[CookieNZ]

I have managed to capture the DelID field being passed into the page, and I want to query the table to get a field called delnotenumber.

Later I want to use the delnotenumber to write to a new table, but at this stage I am purely trying to retrieve it from the db and display in in screen.

 

[jmcilhinney]

Your code is going to produce an SQL statement that looks like this:I think what you actually want is this:

"Select delnotenumber from deliverynote where ID'DelID'"

I think what you actually want is this:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmdstring [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Select delnotenumber from deliverynote where ID[/COLOR][/SIZE][SIZE=2][COLOR=#800000] = '" & DelID & "'"[/COLOR][/SIZE]

although I suggest that you look into using parameterised SQL statements instead of concatenating strings to create literal values.

 

[CookieNZ]

Thanks for that. I am also struggling with the correct syntax to display the field

Is DeliveryNumber.Text = reader.ToString correct?

 

[jmcilhinney]

"reader" is you DataReader, so you don't want to turn that into a string. You need to use it's Item property, which you can index with either the column number or name:

Dim myVal1 As Object = myReader.Item(0) 'Get the value from the column at index zero
Dim myVal2 As Object = myReader.Item("MyCol") 'Get the value from the column named "MyCol"

Note that Item is the default property for the SqlDataReader class, so you can omit the ".Item" if you want. It's not a bad idea to include it at first though, to reinforce to yourself that that's what you are using. When you are more experienced, and less in need of reminding, you'll almost certainly start dropping it to save yourself the extra few key presses.

 

[jmcilhinney]

By the way, you need to call Read on the DataReader to advance it to the next row. To begin with it is not positioned on any row, so the first thing you must do is call Read to position it on the first row. Read returns a Boolean to indicate whether a row was read, so usually you would use a While loop:

While myReader.Read()
    'Use the values from the current row here.
End While