I am trying to pass a parameter to a sql select query.
for some reason, no matter what i try, i keep getting the same error:
input string not in the corect format
and the suggestion:
when converting a string to datetime, parse the string to take the date before putting each variable into the datetime object.
i'm stumped as to what to do. here's the code.
sql query:
ALTER PROCEDURE SearchAllPlayers
(
@personName,
@personPay,
@IDs
)
SELECT DISTINCT
person.personID, sports.sportsID, person.personName, person.personPay, sports.sport,
FROM sports INNER JOIN
person INNER JOIN
personSport ON personSport.personID = person.personID
INNER JOIN
personSport ON personSport.sportID = sports.sportsID
WHERE (person.personName LIKE '%' + @personName + '%')
AND
(person.personPay >= @personPay OR 0 = @personPay)
AND
(person.regDate >= { fn CURDATE() }
AND
(sports.sportID IN
(SELECT ID FROM dbo.fnSplitter(@IDs) AS fnSplitter_1) OR @IDs = 0)
protected void Button1_Click(object sender, EventArgs e)
{
searchGrid();
}
protected void searchGrid()
{
SqlConnection conn = new SqlConnection(connSTR);
com = new SqlCommand();
conn.Open();
com.Connection = conn;
com.CommandText = "SearchAllPlayers";
com.CommandType = CommandType.StoredProcedure;
string StringWithDelimiter = string.Empty;
for (int i = 0; i < DropDownCheckBoxes1.Items.Count; i++)
{
if (DropDownCheckBoxes1.Items[i].Selected)
StringWithDelimiter += DropDownCheckBoxes1.Items[i].Value + ";";
}
com.Parameters.Add("@personName", SqlDbType.VarChar).Value = personName.Text;
com.Parameters.Add("@personPay", SqlDbType.Decimal).Value = ??????
com.Parameters.Add("@IDs", SqlDbType.VarChar).Value = StringWithDelimiter;
sqlDa = new SqlDataAdapter(com);
dS = new DataSet();
sqlDa.Fill(dS);
conn.Close();
GridView1.DataSource = dS;
GridView1.DataBind();
}
so far i've tried the following combinations, all to know avail.
com.parameters.add("@playerPay", sqldbtype.decimal).value = playerPayTextBox.Text;
this doesn't seem to work, i get the following error:
"Error converting data type nvarchar to decimal."
so then i tried this:
com.parameters.add("@playerPay", sqldbtype.decimal).value = playerPayTextBox.tostring();
i get the same error:
"Error converting data type nvarchar to decimal."
which makes sense, so then i tried:
com.parameters.add("@playerPay", sqldbtype.decimal).value = decimal.parse(playerPayTextBox.tostring());
error: "Input string was not in a correct format." - then the datetime thing...
com.parameters.add("@playerPay", sqldbtype.decimal).value = decimal.parse(playerPayTextBox.text);
error: "Input string was not in a correct format." - then the datetime thing...
com.parameters.add("@playerPay", sqldbtype.decimal).value = convert.todecimal(playerPayTextBox.text);
error: "Input string was not in a correct format." - then the datetime thing...
com.parameters.add("@playerPay", sqldbtype.decimal).value = convert.todecimal(playerPayTextBox.text);
error: "Input string was not in a correct format." - then the datetime thing...
not sure what to do