[Hanzie]

Hello, please help, I spended allready hours of internet/google-search!!

I get error-code: Must Declare the scalar variable @BedrijfID

What doing wrong?

	

Private Sub BtnBedrijven_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnBedrijven.Click
		Me.Visible = False
		My.Forms.Hoofdmenu.Show()

	End Sub

	Private Sub BtnTekeningenBedrijf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTekeningenBedrijf.Click
		Dim strsql As String = "select bladnummer, naam, datum from tekeningen where bedrijvenID=@BedrijfID"
		Dim strconnectionstring As String = My.Settings.DocRegDataConnectionString
		Dim objconnection As New SqlConnection(strconnectionstring)

		objconnection.Open()

		Dim objcommand As New SqlCommand(strsql, objconnection)
		Dim SqlParam As New SqlParameter("@BedrijfID", SqlDbType.VarChar, 50)
		sqlparam.value = "2"
		objcommand.Parameters.Add(SqlParam)

		Dim objdataAdapter As SqlDataAdapter
		objdataAdapter = New SqlDataAdapter(strsql, objconnection)

		Dim objdataset As New DataSet
		objdataAdapter.Fill(objdataset, "Tekeningenbedrijven")

		Dim objbindingsource As New BindingSource
		objbindingsource.DataSource = objdataset.Tables("Tekeningenbedrijven")

		TekeningenDataGridView.DataSource = objbindingsource

		objconnection.Close()

	End Sub 

[PsychoCoder]

You're on the right track, but instead of creating a new SqlParameter object, I would just use the AddWithValue Method of the SqlParameterCollection Class. To demonstrate this, take a look at the modifications Ive made to your code. Also, you've got some stuff out of order

Private Sub BtnTekeningenBedrijf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTekeningenBedrijf.Click
       'Create your query as you already have done
        Dim strsql As String = "select bladnummer, naam, datum from tekeningen where bedrijvenID=@BedrijfID"
        'Get your connection string (You've done this right)
        Dim strconnectionstring As String = My.Settings.DocRegDataConnectionString
        'Create your SqlConnection (Done)
        Dim objconnection As New SqlConnection(strconnectionstring)
        'Create your SqlCommand (done)
        Dim objcommand As New SqlCommand()
        '** REMOVE THE FOLLOWING 3 LINES
        'Dim SqlParam As New SqlParameter("@BedrijfID", SqlDbType.VarChar, 50)
        'sqlparam.value = "2"
        'objcommand.Parameters.Add(SqlParam)

        '**Set your command properties**
        objcommand.CommandText = strsql
        objcommand.CommandType = CommandType.Text
        '** USE AddWithValue like so **
        objcommand.Parameters.AddWithValue("@BedrijfID",2)
        objcommand.Connection = objconnection
        'Open your connection
        objconnection.Open()

        

        Dim objdataAdapter As SqlDataAdapter
        objdataAdapter = New SqlDataAdapter(strsql, objconnection)

        Dim objdataset As New DataSet
        objdataAdapter.Fill(objdataset, "Tekeningenbedrijven")

        Dim objbindingsource As New BindingSource
        objbindingsource.DataSource = objdataset.Tables("Tekeningenbedrijven")

        TekeningenDataGridView.DataSource = objbindingsource

        objconnection.Close()

    End Sub 

[Hanzie]

Thnx for youre quick reply.

When I apply as you suggested I still get the folowing error:

Must declare the scalar variable "@BedrijfID".

It refers to line: objdataAdapter.Fill(objdataset, "Tekeningenbedrijven")

Maybe it is the number 2. addwithvalue must have a object-value?!?

When I use the same code with a static sql-query "BedrijfID=2" the code works fine!?!

Very strange, huh?!

This post has been edited by Hanzie: 26 February 2008 - 03:18 PM

[PsychoCoder]

You are indeed right, just pass it in as a variable (it has to be done that way both with your original way and with the AddWithValue method). the AddWithValue method is the preferred way of doing parameterized sql though.

[PsychoCoder]

You are indeed right, just pass it in as a variable (it has to be done that way both with your original way and with the AddWithValue method). the AddWithValue method is the preferred way of doing parameterized sql though.
Also, you're passing it (in your original code) a data tyhpe of char, when I imagine the bedrijvenID column in your database is f Int data type.

Declare a variable for your parameter and pass it in like so:

Private Sub BtnTekeningenBedrijf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTekeningenBedrijf.Click
       'Create a variable to hold your parameter value
        Dim BedrijfID As Integer = 2
       'Create your query as you already have done
        Dim strsql As String = "select bladnummer, naam, datum from tekeningen where bedrijvenID=@BedrijfID"
        'Get your connection string (You've done this right)
        Dim strconnectionstring As String = My.Settings.DocRegDataConnectionString
        'Create your SqlConnection (Done)
        Dim objconnection As New SqlConnection(strconnectionstring)
        'Create your SqlCommand (done)
        Dim objcommand As New SqlCommand()
        '** REMOVE THE FOLLOWING 3 LINES
        'Dim SqlParam As New SqlParameter("@BedrijfID", SqlDbType.VarChar, 50)
        'sqlparam.value = "2"
        'objcommand.Parameters.Add(SqlParam)

        '**Set your command properties**
        objcommand.CommandText = strsql
        objcommand.CommandType = CommandType.Text
        '** USE AddWithValue like so **
        objcommand.Parameters.AddWithValue("@BedrijfID",BedrijfID)
        objcommand.Connection = objconnection
        'Open your connection
        objconnection.Open()

        

        Dim objdataAdapter As SqlDataAdapter
        objdataAdapter = New SqlDataAdapter(strsql, objconnection)

        Dim objdataset As New DataSet
        objdataAdapter.Fill(objdataset, "Tekeningenbedrijven")

        Dim objbindingsource As New BindingSource
        objbindingsource.DataSource = objdataset.Tables("Tekeningenbedrijven")

        TekeningenDataGridView.DataSource = objbindingsource

        objconnection.Close()

    End Sub 

[Hanzie]

Sorry, the same result!?!

I don;t understand it anymore.

Tnx for thinking with me!

HELP

[PsychoCoder]

Well that code came straight from a project I created with a dummy database so the code is tested. I have a feeling you're not using the code I'm providing, or you're forgot to add/remove an idea from the code. Can you post the exact code you're using now (in code tags) because Im real confused since I know the code I posted works (as that is exactly how I do it when working with a database)

[Hanzie]

Because the code is completely adjusted by you, thanks for that, i just copied the whole code in this post and pasted it in my program.

I also believe there are no errors?!!

[Hanzie]

Hello,

In another post i've seen a piece of similar code.
But in this code the declaration for the varable parameter starts with a underscore "_". Something like this:

objcommand.Parameters.AddWithValue("@BedrijfID", _BedrijfID)

Watch: _BedrijfID


Or maybe it is not possible to define the same name for the varable parameter (@BedrijfID) and the value-object (BedrijfID).

I'm not able to try it at the moment.

[PsychoCoder]

Im really confused so I went and made a small change. Below is the code I just copied from a sample project I created to try and find out why you're getting that error message with the code I provided and I'm not. I created a dummy database in SQL Server (I dont know what you're contains so I just copied your column names and put dummy data into it), ran it and got no kind of error. In fat it retrieved my data like I thought it would:

Private Sub BtnTekeningenBedrijf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTekeningenBedrijf.Click
    'Create a variable to hold your parameter value
    Dim id As Integer = 2
    'Create your query as you already have done
    Dim strsql As String = "select bladnummer, naam, datum from tekeningen where bedrijvenID=@BedrijfID"
    'Get your connection string (You've done this right)
    Dim strconnectionstring As String = My.Settings.DocRegDataConnectionString
    'Create your SqlConnection (Done)
    Dim objconnection As New SqlConnection(strconnectionstring)
    'Create your SqlCommand (done)
    Dim objcommand As New SqlCommand()

    '**Set your command properties**
    With objcommand
        .CommandText = strsql
        .CommandType = CommandType.Text
        .Parameters.AddWithValue("@BedrijfID", id)
        .Connection = objconnection
    End With
    'Create a new SqlDataAdapter
    Dim objdataAdapter As New SqlDataAdapter()
    'Create a new DataSet
    Dim objdataset As New DataSet
    'Fill the DataSet using the SqlDataAdapter
    objdataAdapter.Fill(objdataset, "Tekeningenbedrijven")

    Dim objbindingsource As New BindingSource
    objbindingsource.DataSource = objdataset.Tables("Tekeningenbedrijven")

    TekeningenDataGridView.DataSource = objbindingsource

End Sub

So if this code works for me, why isn't it working for is the question I now have to answer.

[Hanzie]

Thank you very much.

When I copy you're "new" code I now get the folowing error:

The SelectCommand property has not been initialized before calling 'Fill'.
The error stands at youre codeline '25'.


Any clue. Why now this error. What is changed?

[PsychoCoder]

Yes I know what this is caused from, I forgot to add something. When using the SqlDataAdapter like I am using it you need to set the SelectCommand Property of your SqlDataAdapter Object to your SqlCommand Object as I do below. Let me know if you receive anymore errors

Private Sub BtnTekeningenBedrijf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTekeningenBedrijf.Click
    'Create a variable to hold your parameter value
    Dim id As Integer = 2
    'Create your query as you already have done
    Dim strsql As String = "select bladnummer, naam, datum from tekeningen where bedrijvenID=@BedrijfID"
    'Get your connection string (You've done this right)
    Dim strconnectionstring As String = My.Settings.DocRegDataConnectionString
    'Create your SqlConnection (Done)
    Dim objconnection As New SqlConnection(strconnectionstring)
    'Create your SqlCommand (done)
    Dim objcommand As New SqlCommand()

    '**Set your command properties**
    With objcommand
        .CommandText = strsql
        .CommandType = CommandType.Text
        .Parameters.AddWithValue("@BedrijfID", id)
        .Connection = objconnection
    End With
    'Create a new SqlDataAdapter
    Dim objdataAdapter As New SqlDataAdapter()
    'Set the SelectCommand property of our adapter
    objdataAdapter.SelectCommand = objcommand
    'Create a new DataSet
    Dim objdataset As New DataSet
    'Fill the DataSet using the SqlDataAdapter
    objdataAdapter.Fill(objdataset, "Tekeningenbedrijven")

    Dim objbindingsource As New BindingSource
    objbindingsource.DataSource = objdataset.Tables("Tekeningenbedrijven")

    TekeningenDataGridView.DataSource = objbindingsource

End Sub

[Hanzie]

THIS IS IT!!! NO ERRORS AT ALL. THANK YOU VERY MUCH FOR YOURE TIME, YOURE FAR TO KIND!!

NOW I CAN MOVE ON WITH MY QUEST!!!

PsychoCoder, on 27 Feb, 2008 - 01:55 PM, said:

Yes I know what this is caused from, I forgot to add something. When using the SqlDataAdapter like I am using it you need to set the SelectCommand Property of your SqlDataAdapter Object to your SqlCommand Object as I do below. Let me know if you receive anymore errors

Private Sub BtnTekeningenBedrijf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTekeningenBedrijf.Click
    'Create a variable to hold your parameter value
    Dim id As Integer = 2
    'Create your query as you already have done
    Dim strsql As String = "select bladnummer, naam, datum from tekeningen where bedrijvenID=@BedrijfID"
    'Get your connection string (You've done this right)
    Dim strconnectionstring As String = My.Settings.DocRegDataConnectionString
    'Create your SqlConnection (Done)
    Dim objconnection As New SqlConnection(strconnectionstring)
    'Create your SqlCommand (done)
    Dim objcommand As New SqlCommand()

    '**Set your command properties**
    With objcommand
        .CommandText = strsql
        .CommandType = CommandType.Text
        .Parameters.AddWithValue("@BedrijfID", id)
        .Connection = objconnection
    End With
    'Create a new SqlDataAdapter
    Dim objdataAdapter As New SqlDataAdapter()
    'Set the SelectCommand property of our adapter
    objdataAdapter.SelectCommand = objcommand
    'Create a new DataSet
    Dim objdataset As New DataSet
    'Fill the DataSet using the SqlDataAdapter
    objdataAdapter.Fill(objdataset, "Tekeningenbedrijven")

    Dim objbindingsource As New BindingSource
    objbindingsource.DataSource = objdataset.Tables("Tekeningenbedrijven")

    TekeningenDataGridView.DataSource = objbindingsource

End Sub

[PsychoCoder]

I'm glad I could help Thats what we're here for