Hello everyone,
I'm trying to port over an application that I created in VB 2005 E to Visual Web Developer 2005 for a web application. I found that it's a little bit different coding for the web, and I'm forced to actually write some code now.
Here is some code that I found to take a value from a row of data and store it in a variable. But I know that the way I'm doing it isn't the most effecient. More specically, I've forced myself to only be able to retrieve one colum of data per query. How can I position my cursor on a record, and be able to choose specific columns to store, rather than run two separate query as below Thanks in advance.
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\websites\mytest\App_Data\FMRadius.mdb" Dim cnnZips As OleDbConnection = New OleDbConnection(strConn) cnnZips.Open() Dim strOleDb As String = "select city from zipcode where zipcode=" & TextBox1.Text.ToString Dim cmdScalar As OleDbCommand = New OleDbCommand(strOleDb, cnnZips) cmdScalar.CommandType = commandtype.text Dim strvalue As String = CStr(cmdScalar.ExecuteScalar) If strvalue <> "" Then Label1.Text = strvalue.ToString strOleDb = "select state from zipcode where zipcode=" & TextBox1.Text.ToString cmdScalar = New OleDbCommand(strOleDb, cnnZips) cmdScalar.CommandType = CommandType.Text strvalue = CStr(cmdScalar.ExecuteScalar) Label2.Text = strvalue.ToString Else Label1.Text = "Null Value Detected - Try Again" TextBox1.Text = "" End If cnnZips.Close()
VWD - Question on how to optimize my code
David Guyer MSFT
Thanks shak! You are right. It should return only one row, unless the United States postal service ever assigns one zip code to multiple cities.
Your help is appreciated.
Regards,
Matt
Patrick van Strien
hi,
welcome back my friend
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\websites\mytest\App_Data\FMRadius.mdb" Dim cnnZips As OleDbConnection = New OleDbConnection(strConn) cnnZips.Open() Dim strOleDb As String = "select city, zipcode from zipcode where zipcode=" & TextBox1.Text.ToString Dim cmdScalar As OleDbCommand = New OleDbCommand(strOleDb, cnnZips) Dim reader as oledbdatareader reader = dbcomm.executereader() If DBreader.Read() Then ' your reader will return single row anyway if its exist 'because zipcode is unique i guessLabel1.Text = reader("city") Label2.Text = reader("zipcode") Else Label1.Text = "Null Value Detected - Try Again" TextBox1.Text = "" End If Reader.Close()
cnnzips.Close() Hope that helps