Parameter Query not accepting Parameter Prefix

Just leave the parameter prefix to blank - it automatically uses @

a prefix would be like Original
then your parameter would be
@Original_Proid

In VS2005, I changed the parameter prefix to blank and went back into the TableAdapter Query Configuration wizard and added "Where ProposalID = @Proid" and got the same error.  :(

Here is my Query:
------
SELECT ProposalID, Address, Lot, Block, Sub, Status, AcceptDate, jobPhone, JobName, Homeowner, preparedbY, ProType, PONum, Juris, City, State, Zip, Directions, Architect, Notes, JobNum, Cusnum, name, address1, address2, address3, notes1, notes2, notes3, phonenum, salesrepnum, contact, addresscity, addressstate, addresszip, faxnum FROM dbo.vJobSetup
where ProposalID = @Proid
------

Nothing too special, but getting frustrated on getting this query to accept a parameter...

Thanks!

do you have your @Proid parameter defined? In the parameters collection of the Select command make sure you have the parameter existent and that the sourcecolumn and dbtype is correct.

HTH
~BC

I setup the following:

Parameter Name: ProID
source Column: ProposalID
Provider Type: BigINT

Those are the ones I changed, the rest are the default values.

When I go to preview the data, the error comes up with:  "Must declare the scalar variable @ProID"

I would still get the same error while in the design mode also.

"I'm starting to dislike VS2005"

well - that's what you would expect though - you must define the value of @ProID at some point - so the select actually filters on that clause

In your form, you need to bind that parameter to either a control or set its value programatically

How do you have your table adapter defined in your form?

Usually, parameters are used in Update, Delete and Insert not very often for select. To filter, you usually just perform the filter on the bindingsource

so it'll be

BindingSource1.Filter = "ProposalID=1000"

On the form I dragged a dataadapter to my form.
The line of code:

Me.VJobSetupTableAdapter.FillBy(Me.JobSetupDataSet.vJobSetup)

was put in the form Load.  I have not found a way to place a filter on this form and have tried numerious things.

So what I did did was build another query from the method that I talked about above using another "Fillby" (FillbyProid) and trying to add the parameter in the query etc.

--This problem goes hand-in-hand with my open question "Form - Dataset Parameters (filter)"--
This will answer some of your questions on what I am trying to do, but I was trying to go about it a different way, and still coming up empty handed.

Please advise if there is a simpler way to get one record out of a dataset by passing a ID etc.
Thanks!

Ok in your dataset designer

Select your tableadapter
Select your method (usually "Fill, GetData")
or Add a new method

In the properties, change your Select query there to
SELECT ......... FROM .... WHERE ProductID = @ProID

This should automatically repopulate your parameter collection
Make sure execute is set to "Reader"

Then in your code
Assume:
your data layer namespace is "MyApplication.MyDataSet"
your data adapter is "myDataAdapter"
your data table is "myDataTable"

Then in your code

Dim myDA As New MyApplication.MyDataSet.myDataAdapter
Dim myDT As MyApplication.MyDataSet.MyDataTable = myDA.GetData(12) ' Where 12 is the ID you want

or you could have
myDA.Fill(myDT, 12)

whichever one you are most familiar and comfortable with.

so what it does?
You are passing 12 as @ProId into your Select Query
The select query is running (using the adapter which is interfacing with the DB) spits out the result into a datatable - Since it seems you have a strongly typed dataset it makes more sense to create an object myDT of type MyDataTable since that'll reduce any late-binding bugs

Hope this makes some sense - at least the gist of it

~BC

Running the code it stops on the fill command and shows the error
"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@proid"."

----
Me.VJobSetupTableAdapter.FillByProID(Me.JobSetupDataSet.vJobSetup, 2064)  'I put in a hard code number just to get it passed accepting a varable.
----

I've tried a few ways to slip in that parameter into the select statement and it just does not want to except a varable to be passed.  Is this a VS2005 problem or SqlServer2005 problem?

I did some digging around and found that things look good in the VS code etc.  But I am still don't understand why I would get the problem with trying to use @ in my query to pass the parms etc.

Here is the code that I found:

------
       <System.Diagnostics.DebuggerNonUserCodeAttribute(),  _
         System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter"),  _
         System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Fill, false)>  _
        Public Overloads Overridable Function FillByProID(ByVal dataTable As JobSetupDataSet.vJobSetupDataTable, ByVal Proid As Long) As Integer
            Me.Adapter.SelectCommand = Me.CommandCollection(1)
            Me.Adapter.SelectCommand.Parameters(0).Value = CType(Proid,Long)
            If (Me.ClearBeforeFill = true) Then
                dataTable.Clear
            End If
            Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
            Return returnValue
        End Function
--------

Hi

Dont know if you have tried this but when using a ODBC connection string you should you a '?' instead of '@paramname'
i.e. "SELECT * FROM users WHERE idUser  = ?"
cmd.parameters.addwithvalue("?", aValue)

i recently had this problem and using the ? sorted it.

HTH
o

I added:

FROM         vJobSetup where ProposalID = ?proid

still getting the error "Error in WHERE clause 'proid'
???????

Just putting the ? did work.  I am still having problems trying to qualify the parameter, but I can move on with this solution.

Thanks!

Problem solved!

The problem was that I was using System.Data.Odbc.  This method does not accept named parameters
Instead I used System.Data.SQLClient and this method does use named parameters.  All is well.