Stored Procedures/web page

[tommyarcher]

I'm trying to create a webpage that will allow the user to input a vendor number(text box) and if the number exists populate the form with vendor name, phone and state using a stored procedures. Below you will find the stored procedures and after it my webpage.

Stored Procedures:
SELECT [V_NAME], [V_PHONE], [V_STATE] FROM [VENDOR] WHERE ([V_CODE] = @V_CODE)

vb code:

Protected Sub btnVendor_Click(sender As Object, e As System.EventArgs) Handles btnVendor.Click
        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyDataAdapter As SqlDataAdapter






        MyConnection = New SqlConnection("server=xx.xx.xx.xx;database=xxx;User ID=xxxx; Password=xxxx")

        MyDataAdapter = New SqlDataAdapter("VendorInfo", MyConnection)

        MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@V_CODE", txtVenNum.Text))

        MyDataAdapter.SelectCommand.Parameters("@V_CODE").Value = Trim(txtVenNum.Text)

        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@V_NAME", SqlDbType.NVarChar, 15))
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@V_Phone", SqlDbType.NVarChar, 8))
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@V_State", SqlDbType.NVarChar, 2))
        DS = New DataSet()
        MyDataAdapter.Fill(DS, "Results")


        MyDataAdapter.Dispose()
        MyConnection.Close()
        lblOutput.Text = DS.Tables("Results").Rows(0).Item(1)
        lblout1.Text = DS.Tables("Results").Rows(0).Item(2)
        lbloutput2.Text = DS.Tables("Results").Rows(0).Item(3)



       
    End Sub
End Class

Can someone help me out with this please. Thanks in advance.

[darkagn]

Hi tommyarcher and welcome to DaniWeb :)

Are you sure you want to use a stored procedure? Your sample looks like a simple query rather than a SP, which is defined like so:

CREATE PROCEDURE SelectVendors
(
   @VCode INT
)
AS
SELECT [V_NAME], [V_PHONE], [V_STATE] FROM [VENDOR] WHERE ([V_CODE] = @V_CODE)

This SP would be called in C# (sorry I don't know VB) like so:

SqlConnection cnn = new SqlConnection(connectionString);
cnn.Open();
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SelectVendors";
SqlParameter param = cmd.CreateParameter();
param.Name = "@VCode";
param.DbType = SqlDbType.Int32;
param.Value = 64;

// execute the stored procedure and return the data in a reader
SqlDataReader reader = cmd.ExecuteReader();
// generate a list of "Vendor" objects to hold the data
List<Vendor> list = new List<Vendor>();
while (reader.Read())
{
   Vendor v = new Vendor();
   v.Name = reader["V_NAME"].ToString();
   v.Phone = reader["V_PHONE"].ToString();
   v.State = reader["V_STATE"].ToString();
   list.Add(v);
}

// use list to do something useful
// ...
cnn.Close();

If you're not using a stored procedure, change the command type to CommandType.Text and set the command text to the query.

Hope this helps, good luck :)

[tommyarcher]

Thanks for the help. I'm working on it but I think it will work. For some reason when I try to make the new 'Vendor list' it keeps giving me an error that it is not defined. I will work on it when I return from running out for a bit. Again, thanks for everything. I can learn a lot from you. Have a good day.