MySQL placeholder in visual studio 2008

Hi,

I am using visual studio 2008 with mysql.

I want to include a parameter in my fill query.

When I specify

SELECT * from 'table' WHERE 'tableid' =?param1

VS tells me there's an error with my syntax.

When I specify

SELECT * from 'table' WHERE 'tableid' =@param1

then the resulting fill method expects a string.

What's the correct placeholder for an INTEGER parameter?
MDWinterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jorge PaulinoIT Pro/DeveloperCommented:
That doesn't mean that is an Integer, that indicates that you're using a parameter in the T-SQL command.
@ is used to define a parameter in SQL, mySQL, etc
? is used to define a parameter in Access (OleDb).
In Access isn't really a parameter, is just an indication that will be used a value.
0
MDWinterAuthor Commented:
Really?

How come when I use the code:

            myDataSet.competitionbyseasonDataTable dt = new myDataSet.competitionbyseasonDataTable();
            int x = 12;
            competitionbyseasonTableAdapter1.FillByCompID(dt, x);

I get an error:

"Argument 2 cannot change int to string"

yet the code:

            myDataSet.competitionbyseasonDataTable dt = new myDataSet.competitionbyseasonDataTable();
            string x = "12";
            competitionbyseasonTableAdapter1.FillByCompID(dt, x);

works fine?

There must be some way of passing an integer to the mySQL query?


0
itsmeandnobodyelseCommented:
>>>> There must be some way of passing an integer to the mySQL query?

If the FillByCompID expects a string for second argument you can't pass an int variable. So you either add a second FillByCompID which takes an int or you convert the integer to a string (like you did) what is a perfect way for a sql query to pass numbers as an sql statement is pure text anyhow.

Note, if the FillByCompID was created by wizard it probably has a string for second argument because the field is a varchar and not a number.
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

MDWinterAuthor Commented:
The query was created with the wizzard. But when I go back to the designer and click on the properties for that field, it lists it's datatype as system.int32

I don't understand how passing it as a string is any good, since the field is an INT in the database.

Can you explain how I would add a second fillbycompID which takes an int?
0
itsmeandnobodyelseCommented:
>>>> how passing it as a string is any good, since the field is an INT in the database
See the following statement:

  select * from myt where id=12345

You see that the 12345 is a number but nevertheless it is a string within the above sql query.

>>>> Can you explain how I would add a second fillbycompID which takes an int?

If you post the code of the current FillByCompID I will do it. I don't know much of C# as my language is C++.
0
MDWinterAuthor Commented:
OK - my database table looks like this:

CompBySeasonID(int)       Competition_CompetitionID(int)       Season_SeasonID(int)
7                                      12                                                    16
6                                      3                                                      12

and my mySQL query is

SELECT     CompBySeasonID, Competition_CompetitionID, Season_SeasonID
FROM         competitionbyseason
WHERE     ('Competition_CompetitionID' = @comp)

and the code for FillByCompID is:

        public virtual int FillByCompID(soccersiteDataSet.competitionbyseasonDataTable dataTable, string comp) {
            this.Adapter.SelectCommand = this.CommandCollection[1];
            if ((comp == null)) {
                throw new global::System.ArgumentNullException("comp");
            }
            else {
                this.Adapter.SelectCommand.Parameters[0].Value = ((string)(comp));
            }
            if ((this.ClearBeforeFill == true)) {
                dataTable.Clear();
            }
            int returnValue = this.Adapter.Fill(dataTable);
            return returnValue;
        }

When I run the following code:

int x = 12
competitionbyseasonTableAdapter1.FillByCompID(dt, x.ToString());
int y = dt.count;
MessageBox.Show(y.ToString());

I get 0.

When I run
competitionbyseasonTableAdapter1.Fill(dt) I get the correct row count of 2. So the FillbyCompID method is obviously not getting any match.



0
itsmeandnobodyelseCommented:
>>>> public virtual int FillByCompID(soccersiteDataSet.competitionbyseasonDataTable dataTable, string comp)

Here you see that the second argument is of type string. Hence you couldn't pass an int but only the number as a string.

>>>> this.Adapter.SelectCommand.Parameters[0].Value = ((string)(comp));

The next is that the 'Value' member of the structure used to build the query also is a string. The goal is to construct an SQL statement out of the settings made to 'Adapter.SelectCommand'. And cause such a select statement is pure text they also want the inputs as text what spares conversion.


>>>> Can you explain how I would add a second fillbycompID which takes an int?      

 public virtual int FillByCompID(soccersiteDataSet.competitionbyseasonDataTable dataTable, int comp) {
            this.Adapter.SelectCommand = this.CommandCollection[1];
            // assuming that -1 is not a valid ID
            if ((comp == -1)) {
                throw new global::System.ArgumentNullException("comp");
            }
            else {
                this.Adapter.SelectCommand.Parameters[0].Value = comp.ToString();
            }
            if ((this.ClearBeforeFill == true)) {
                dataTable.Clear();
            }
            int returnValue = this.Adapter.Fill(dataTable);
            return returnValue;
        }

Or shorter:

public virtual int FillByCompID(soccersiteDataSet.competitionbyseasonDataTable dataTable, int comp)
{
      return  FillByCompID(dataTable, comp.ToString());
}


>>>> So the FillbyCompID method is obviously not getting any match.

The difference between both calls probably is here

      if ((this.ClearBeforeFill == true)) {
                dataTable.Clear();
 
That statement would clear the dataTable if the ClearBeforeFill is true.


I can't see why the 'count' member should change with your calls to  FillbyCompID.

Isn't the 'count' the result of a query? The  FillbyCompID only fills the where clause but doesn't make a query, right?




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MDWinterAuthor Commented:
That's done the trick - many thanks :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.