Monday, March 19, 2012
error on building up where clause statement
build up a whereclause ?
error message
--
syntax error converting the varchar value ' and IDUser >= 1 ' to a column of
data type int
sproc
--
...
IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser = '
+ @.ClientID END
IF @.ClientID = -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser >= 1
' END
...SQL Server thinks that you want to mathematical add two numbers together
because there is a INT in the addition. To Keep sure to concat the two
strings together you have to cast the INT value to a character type like
varchar(10), so the whole expression should be:
> IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser =
> ' + CAST(@.ClientID AS VARCHAR(50)) END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:e9a6rJuaFHA.584@.TK2MSFTNGP15.phx.gbl...
> how can I resolve this error message coming from the line of code used to
> build up a whereclause ?
> error message
> --
> syntax error converting the varchar value ' and IDUser >= 1 ' to a column
> of data type int
> sproc
> --
> ...
> IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser =
> ' + @.ClientID END
> IF @.ClientID = -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser >=
> 1 ' END
> ...
>|||thanks for replying, I tried your suggestion but I still get the same error
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e5hlJguaFHA.3572@.TK2MSFTNGP12.phx.gbl...
> SQL Server thinks that you want to mathematical add two numbers together
> because there is a INT in the addition. To Keep sure to concat the two
> strings together you have to cast the INT value to a character type like
> varchar(10), so the whole expression should be:
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "TJS" <nospam@.here.com> schrieb im Newsbeitrag
> news:e9a6rJuaFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||Can you send in he whole statements and the exact error to see where the
Parser throws the error ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:OKz0PDvaFHA.2768@.tk2msftngp13.phx.gbl...
> thanks for replying, I tried your suggestion but I still get the same
> error
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:e5hlJguaFHA.3572@.TK2MSFTNGP12.phx.gbl...
>|||Could it be that @.WhereClause is declared as integer?
Ral Moloye|||here is current version, the purpose of the sproc is to return a list if
matching record id's
based on filter parameters provided.
========================================
============================
Alter Procedure "ListID"
(
@.admin nvarchar(10),
@.YearID int,
@.ClientID int,
@.ProjectID int
)
As
DECLARE @.List varchar(100)
DECLARE @.WhereClause nvarchar (1000)
DECLARE @.CastString1 varchar (50)
DECLARE @.CastString2 varchar (50)
SET @.WhereClause=''
SET @.CastString1 = ' and IDuser >= ' + CAST(@.ClientID AS VARCHAR(50))
SET @.CastString2 = ' and IDuser = ' + CAST(@.ClientID AS VARCHAR(50))
-- Define the selection criteria for client depending on whether or not
user is an admin
-- ----
---
IF @.admin = 'true' and @.ClientID = -1 BEGIN SET @.WhereClause =
@.WhereClause + @.CastString1 END
IF @.admin = 'true' and @.ClientID <> -1 BEGIN SET @.WhereClause =
@.WhereClause + @.CastString2 END
IF @.admin = 'false' BEGIN SET @.WhereClause = @.WhereClause +
@.CastString2 END
-- Define the selection criteria for selections of project and year
-- ----
---
IF @.ProjectID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDProject
= ' + @.ProjectID END
IF @.YearID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
year(issueDate) = ' + @.YearID END
-- retrieve selection
-- ----
---
SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
FROM invoices, Projects
WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause
-- return results
-- ----
---
SELECT @.List|||Everywhere where a INt takes place (doesntmatter if you declared it in your
procedure or you get it from the database) and you want to add it to the
string, SQL Server will try to do a methematical addition to the strings
which wont work, so you will have to replace that in your whole code (e.g.
For YearID, projectId ...)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:uVOaKl4aFHA.3840@.tk2msftngp13.phx.gbl...
> here is current version, the purpose of the sproc is to return a list if
> matching record id's
> based on filter parameters provided.
> ========================================
============================
> Alter Procedure "ListID"
> (
> @.admin nvarchar(10),
> @.YearID int,
> @.ClientID int,
> @.ProjectID int
> )
> As
> DECLARE @.List varchar(100)
> DECLARE @.WhereClause nvarchar (1000)
> DECLARE @.CastString1 varchar (50)
> DECLARE @.CastString2 varchar (50)
> SET @.WhereClause=''
> SET @.CastString1 = ' and IDuser >= ' + CAST(@.ClientID AS VARCHAR(50))
> SET @.CastString2 = ' and IDuser = ' + CAST(@.ClientID AS VARCHAR(50))
> -- Define the selection criteria for client depending on whether or not
> user is an admin
> -- ----
---
> IF @.admin = 'true' and @.ClientID = -1 BEGIN SET @.WhereClause =
> @.WhereClause + @.CastString1 END
> IF @.admin = 'true' and @.ClientID <> -1 BEGIN SET @.WhereClause =
> @.WhereClause + @.CastString2 END
> IF @.admin = 'false' BEGIN SET @.WhereClause = @.WhereClause +
> @.CastString2 END
> -- Define the selection criteria for selections of project and year
> -- ----
---
> IF @.ProjectID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
> IDProject = ' + @.ProjectID END
> IF @.YearID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
> year(issueDate) = ' + @.YearID END
> -- retrieve selection
> -- ----
---
> SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
> FROM invoices, Projects
> WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause
> -- return results
> -- ----
---
> SELECT @.List
>
Wednesday, March 7, 2012
Error message?
Hi,
I have an error message:
Exception Details:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.
It doesnt say which line of code it is reffering to however..
Could someone help me?
The parameter for @.qty is Textbox1.Text - maybe I could make the text box int only or something?
My code is:
private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");
command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - @.qty WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();
con.Close();
return true;
}
private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");
command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES ('@.User','@.productID','@.Itemsname',@.date,'@.qty')";
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();
con.Close();
return true;
}
protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}
protected void Button2_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "Update")
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteInsert(TextBox1.Text);
}
}
Thanks to anyone who can help!!!
Jon
jbear123:
Exception Details:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.
Well, the error clearly says that the server is expecting int type value for the parameter called @.qty and you are providing either nothing or some value which is not int for @.qty.
First, make sure that the value in your textbox TextBox1 in of type int.
Second, for better confirmation, pass the cast(ed) value to the @.qty parameter, like Convert.ToInt32(TextBox1.text). This way you'll immediately get notified on the line where you try to convert it to int if the textbox is holding non int values.
jbear123:
command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES ('@.User','@.productID','@.Itemsname',@.date,'@.qty')";
I don't know whether this line is being executed or not, but anyway you'll get an error if at all this line gets executed. The problem is in the insert query you've written. You don't need to add ' (single quotation) around the parameters. It is required only if you're passing the values directly in the query.
Hope this will help.
|||Hi,
dhimant:
First, make sure that the value in your textbox TextBox1 in of type int.
Just make sure the value typed in is int? Or do i have to edit the settings to make the textbox int only?
The value typed in is always int..
dhimant:
Second, for better confirmation, pass the cast(ed) value to the @.qty parameter, like Convert.ToInt32(TextBox1.text). This way you'll immediately get notified on the line where you try to convert it to int if the textbox is holding non int values.
Where should I put the Convert.ToInt32(TextBox1.text) code?
dhimant:
I don't know whether this line is being executed or not, but anyway you'll get an error if at all this line gets executed. The problem is in the insert query you've written. You don't need to add ' (single quotation) around the parameters. It is required only if you're passing the values directly in the query.
Thanks! Done!
Thanks alot,
Jon
|||
jbear123:
Where should I put the Convert.ToInt32(TextBox1.text) co
Just where you're setting the value for the command parameter value. Instead of just TetBox1.Text write Convert.ToInt32(TextBox1.Text).
Hi,
I had tried that it gives me the same error:
System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.
..?
Cheers,
Jon
||| Now, can you re-post the all modified code again ? I don't see any problem if you've made all the correction. One more thing, Why are you using both button2_command and click events and what are you trying to do with them ?
Hi,
Code:
private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");
command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - '@.qty' WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();
con.Close();
return true;
}
private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");
command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES (@.User,@.productID,@.Itemsname,@.date,@.qty)";
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", Convert.ToInt32(TextBox1.Text));
command.Parameters.Add("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();
con.Close();
return true;
}
protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}
protected void Button2_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "Update")
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteInsert(TextBox1.Text);
}
}
}
dhimant:
One more thing, Why are you using both button2_command and click events and what are you trying to do with them ?
I used them to initiate update and insert, someone suggested that I didnt put them both in button click? Should I?
Thanks again!
Jon
Sunday, February 26, 2012
Error Message with Recordset.Update
Hi,
I am converting a VB6 application to C# using ADO. I have the following code:
private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();
private connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= " + stdDir;
strClientSQL = "SELECT * FROM Client ORDER BY casename, renldate, product, fundingcode";
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cn.Open(Utilities.connectionString,null,null,0);
rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
rs.Open(strClientSQL,cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic,-1);
rs.Fields["CaseNo"].Value = this.txtCaseNumber.Text;
rs.Update();
When I try to compile and run it I get the following error message:
"No overload for method 'Update' takes '0' arguments"
I am not sure if I need to enter information in the update command or do something else. This seems pretty straight forward but I can't figure out how to do it. Can someone please help me?
Thanks,
Mark
The thing is that you have to place the fieldas and its values in the function, like this:rsMsg.Update("Field1,Field2,Field3", "Value1,Value2,Value3");
But i supossed that you want to update the whole recordset, am i rigth?
That's a good question.... give me some time to answer that.
Hugo|||try without the parenthesis
rs.Update
Error Message with Recordset.Update
Hi,
I am converting a VB6 application to C# using ADO. I have the following code:
private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();
private connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= " + stdDir;
strClientSQL = "SELECT * FROM Client ORDER BY casename, renldate, product, fundingcode";
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cn.Open(Utilities.connectionString,null,null,0);
rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
rs.Open(strClientSQL,cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic,-1);
rs.Fields["CaseNo"].Value = this.txtCaseNumber.Text;
rs.Update();
When I try to compile and run it I get the following error message:
"No overload for method 'Update' takes '0' arguments"
I am not sure if I need to enter information in the update command or do something else. This seems pretty straight forward but I can't figure out how to do it. Can someone please help me?
Thanks,
Mark
The thing is that you have to place the fieldas and its values in the function, like this:rsMsg.Update("Field1,Field2,Field3", "Value1,Value2,Value3");
But i supossed that you want to update the whole recordset, am i rigth?
That's a good question.... give me some time to answer that.
Hugo|||try without the parenthesis
rs.Update
Sunday, February 19, 2012
Error Message conversion failed when converting the nvarchar value to data type int?
Hi,
I can't seem to fix the following error in my stored procedure.
Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.
The line of code in my stored procedure that seems to be the problem is the following:
CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END
Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.
hi,
Aston35 wrote:
Hi,
I can't seem to fix the following error in my stored procedure.
Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.
The line of code in my stored procedure that seems to be the problem is the following:
CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END
Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.
as you pointed out, the 2 columns are of a different data type, so you have to deal accordingly.. as you can not convert 'abc' to an integer data type, you have to perform the opposite, convert the int to a varchar...
SET NOCOUNT ON;DECLARE @.b int;
SET @.b = 0;
PRINT 'works';
SELECT CASE WHEN @.b = 0 THEN 1 ELSE 'a' END AS [Result];
SET @.b = 1;
PRINT 'does not work';
SELECT CASE WHEN @.b = 0 THEN 1 ELSE 'a' END AS [Result];
GO
DECLARE @.b int;
SET @.b = 1;
PRINT 'works';
SELECT CASE WHEN @.b = 0 THEN CONVERT(varchar, 1) ELSE 'a' END AS [Result];
--<
works
Result
--
1
does not work
Result
--
Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value 'a' to data type int.
works
Result
a
regards|||Thanks Andrea, will give your suggestion ago and see how i get on.