Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

mulefeathers
Starting Member

12 Posts

Posted - 2012-11-13 : 12:21:57

I am tryign to return the values from a table based on the selected value of a combobox. This code is in a SelectedIndexChanged event of a combobox. If anyone could please read this code and tell me where my error is and point me the correct direction.
I declare my variables, create a connection string and open.


Dim strprocess As String
        Dim strpart As String = Nothing
        strprocess = cboprocess.SelectedValue
        Dim connectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\sql_dbf\kanban card.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

        strpart = "SELECT PartNumber FROM tblParts WHERE Process = @Process"

       
        Dim conn1 As New SqlConnection(connectionString)
        conn1.Open()

Then I am using the next block of code (I think) to create a data table to hold the results of the query.


Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(strpart)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@Process", strprocess)
        cmd.Connection = conn1

        Dim someDatatable As New DataTable
        Dim myReader As SqlClient.SqlDataReader = cmd.ExecuteReader

        If myReader.HasRows Then
            someDatatable.Load(myReader)
        End If

But when I try to compile I get an error:
The parameterized query '(@Process nvarchar(4000))SELECT PartNumber FROM tblParts WHERE P' expects the parameter '@Process', which was not supplied.

Thanks in advanced for any help.

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-13 : 12:37:39

Try this instead of AddWithValue:

cmd.Parameters.Add("@Process", SqlDbType.Int)
cmd.Parameters("@Process").Value = strprocess

-Chad

mulefeathers
Starting Member

12 Posts

Posted - 2012-11-13 : 15:20:37

Thanks, its seams to be getting there. This line of code:

Dim myReader As SqlClient.SqlDataReader = cmd.ExecuteReader

Is giving error:
Failed to convert parameter value from a String to a Int32.

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-13 : 15:30:59

Do Int.Parse on it

-Chad

mulefeathers
Starting Member

12 Posts

Posted - 2012-11-14 : 09:49:07

Could you explain the purpuse of converting the string to an interger?

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-14 : 10:34:22

I assumed it was an integer since there were no single quotes in the query. Is Process a string?

-Chad