Join 150,072 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 1,788 people online right now. Registration is fast and FREE... Join Now!
Hello, please help, I spended allready hours of internet/google-search!!
I get error-code: Must Declare the scalar variable @BedrijfID
What doing wrong?
CODE
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")
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
vb
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")
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.
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:
vb
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")
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)
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:
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:
vb
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")
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
vb
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")
26 Feb, 2008 - 01:32 PM
| 