'Using Query strings in a SQL DB



'Default.aspx



Dim SelectedProduct As String


    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        FillProducts()
        FillCustomers()

    
    End Sub
    Private Sub FillProducts()
        If Not IsPostBack Then

            Try

                Dim strSQL As String
                strSQL = "SELECT * from Products ORDER BY Descrip ASC"

                Dim strConnectionString As String
                strConnectionString = ConfigurationSettings.AppSettings("ConnectionString")

                Dim Conn As New SqlConnection(strConnectionString)
                Dim cmd As New SqlCommand(strSQL, Conn)
                Dim MyReader As SqlDataReader

                Conn.Open()

                '==========fill the products list box===========

                '---datareaders read 1 line at a time so put them in a loop
                MyReader = cmd.ExecuteReader()
                While MyReader.Read()
                    lstProducts.Items.Add(MyReader.Item("descrip").ToString())
                End While
                MyReader.Close()
                Conn.Close()

            Catch

                lblError.Visible = True
                lblError.Text = "I cannot read the products table. " & Err.Description

            End Try
        End If
    End Sub

    Private Sub FillCustomers()
        If Not IsPostBack Then

            Try

                Dim strSQL_customer As String
                strSQL_customer = "SELECT CustID, FName, LName from customers ORDER BY LName"

                Dim strConnectionString As String
                strConnectionString = ConfigurationSettings.AppSettings("ConnectionString")

                Dim Conn As New SqlConnection(strConnectionString)
                Dim cmd As New SqlCommand(strSQL_customer, Conn)
                Dim MyReader As SqlDataReader

                Conn.Open()

                '==========fill the customers list box===========
                'I read CustID (item 0) into scratch variable
                'I read FName (item 1) & append to scratch variable
                'I read LName (item 2) & append to scratch variable to show full name.
                'Be sure to include all the fields you want to view SQL string and refer to them
                'in order starting with 0

             
                Dim temp As String

                MyReader = cmd.ExecuteReader()
                While MyReader.Read()

                    temp = MyReader.GetValue(0).ToString    'data type in table = int so use getvalue
                    temp &= "  " & MyReader.GetString(1).ToString  'data type in table = char so use getstring
                    temp &= MyReader.GetString(2).ToString  'data type in table = char so use getstring
                    lstCustomer.Items.Add(temp)
                End While

                MyReader.Close()
                Conn.Close()

            Catch

                lblError.Visible = True
                lblError.Text = "I cannot read the customer table. " & Err.Description

            End Try
        End If

    End Sub

    Private Sub btnDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDetails.Click
        If lstProducts.SelectedIndex <> -1 Then
            Response.Redirect("products.aspx?product=" & Trim(SelectedProduct))
        Else
            lblError.Visible = True
            lblError.ForeColor = Color.Red
            lblError.Text = "Please select an item"

        End If

    End Sub

    Private Sub lstCustomer_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCustomer.SelectedIndexChanged
        CustName = Trim(Mid(lstCustomer.SelectedItem.Text, 4))
        Session("CustName") = CustName

    End Sub

    Private Sub lstProducts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstProducts.SelectedIndexChanged
        SelectedProduct = Trim(lstProducts.SelectedItem.Text)
    End Sub
End Class




'------------------------------------------------------


'                         PRODUCTS.ASPX


'------------------------------------------------------

'This form shows the details of the chosen product in a datagrid. It finds the correct record
'by using the product description I sent it in the URL redirect statement. It takes the following
'statement to extract the info from the URL:

'                Dim SelectedProduct As String = Trim(Request.QueryString("product"))

'I used the term "product" in the URL when I sent it and get it back here using the same reference then
'I just assign it to a local variable.

'Updated 11/27/05




Imports System.Data
Imports System.Data.SqlClient


Public Class products
    Inherits System.Web.UI.Page
 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ShowDetails()
    End Sub
    Private Sub ShowDetails()

        Try
            Dim SQL_Product As String
            Dim SelectedProduct As String = Trim(Request.QueryString("product"))


            lblCustomerName.Text = Session("CustName")

            SQL_Product = "SELECT ProdID, Descrip, Price from Products Where Descrip = '" & Trim(SelectedProduct) & "'"

           
            Dim strConnectionString As String
            strConnectionString = ConfigurationSettings.AppSettings("ConnectionString")

            Dim Conn As New sqlConnection(strConnectionString)
            Dim cmd As New SqlCommand(SQL_Product, Conn)


            Conn.Open()

            DataGrid1.DataSource = cmd.ExecuteReader
            DataGrid1.DataBind()
            Conn.Close()

        Catch

            lblError.Visible = True
            lblError.Text = "I cannot find that product. " & Err.Description


        End Try
    End Sub

    Private Sub btnReturn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReturn.Click
        Response.Redirect("default.aspx")
    End Sub
End Class




               CONN STRING IN WEB CONFIG

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
     <appSettings>
	
	<add key="ConnectionString" value="Server=(local);database=customers;uid=aspuser;pwd=123456" />
		</appSettings>    
  
  <system.web>