How to declare a parameter with the value of a TextBox1.Text

bex · June 24th, 2009, 02:59 PM

Hi there i am trying to develop a small windows desktop app, All it needs to do is query a sql2005 database look 4 whatever is in the TextBox and display it in a Grid View.
I am using VS2008 and VB, what i have so far is a Dataset that pulls data from 2 tables then have a stored procedure that :
SELECT Customers.CustomerID, Customers.CompanyName, Products.ProductName
FROM Customers CROSS JOIN
Products
WHERE (Customers.CustomerID = '22')

this works only 4 ID 22, then i did
WHERE (Customers.CustomerID = '" & TextBox1.Text"')did not work

WHERE (Customers.CustomerID = @CustomersId)

now where do i assign the @CustomerID=TextBox1.text??

thanks in advance

Got it it was same as Web form under buytton event.

gbianchi · June 25th, 2009, 08:29 AM

Hi there..

For @Parameter you have a parameter collection in the command object that execute the SP, where you can add parameters (type, name, value).

bex · June 25th, 2009, 08:43 AM

Hi i am having truble to call it ?

I have this

Dim sqlcom AsNew SqlCommand
sqlcom.Connection = SqlConnection
sqlcom.CommandType = CommandType.StoredProcedure
sqlcom.CommandText = "AccountSelectCommand"
sqlcom.Parameters.Add("@Account", SqlDbType.Char, 8)
sqlcom.Parameters("@Account").Value = TextBox1.Text

But i cant find the connection?

gbianchi · June 25th, 2009, 08:52 AM

I don't understand you.. what connection?

bex · June 25th, 2009, 09:43 AM

ok I have a dataset that searches 2 tables
this is my stored procedure:
ALTER PROCEDURE dbo.AccountSelectCommand
AS
SET NOCOUNT ON;
SELECT table1.CUSN05, table1.CNAM05, table2.SimNet, table2.Cost, table2.Commission
FROM table1 CROSS JOIN
table2
WHERE (table1.CUSN05 = '@Account')

then i have btnSearch and txtSearch

Now how do i call this procedure from the btn Search to disply the matching data in a DataGrid.

gbianchi · June 25th, 2009, 11:41 AM

You wrote the code to call it just fine in your last post.. I don't understand your problem...

But, doesn't you need to define the parameters that the Store procedure receive???

bex · June 25th, 2009, 12:17 PM

it was working fine when i dearched a table now i am searching the dataset

now i fet this error

ExecuteScalar requires an open and available Connection. The connection's current state is closed.

with this code:
Dim sqlcom AsNew SqlCommand
sqlcom.Connection = conn
sqlcom.CommandType = CommandType.StoredProcedure
sqlcom.CommandText = "AccountSelectCommand"
sqlcom.Parameters.Add("@Account", SqlDbType.Char, 8)
sqlcom.Parameters("@Account").Value = txtAccount.Text
Dim Da As SqlDataAdapter = New SqlDataAdapter()
Da.SelectCommand = sqlcom
Dim Ds As DataSet = New DataSet
'Da.Fill(Ds, "Cusn05")
DataGridViewSearch.DataSource = Da

Try
MessageBox.Show(sqlcom.ExecuteScalar.ToString)
Catch Sqlex As SqlException
MessageBox.Show(Sqlex.Message)
EndTry

gbianchi · June 25th, 2009, 12:22 PM

This is like mad people conversation...

Let's try to order this...

The SP needs to define the parameter...
The code you wrote call the SP just fine...
You have problems connecting to the database???

bex · June 25th, 2009, 02:41 PM

The SP working with this
Me.NewSelectCommand1TableAdapter.FillBy(Me.TrackingDataSet.NewSelectCommand1)

so where do i add the parameter here?

i created the dataset then SP then went to DataGrid clicked the smart tag and choose datasorce.
This way works without parameter. when i try to hand code the event it gives me th errors.

what is the best approach or is there any book that talks about this?