hello,
I am trying to insert date and time into my table.
insert into <table_name> values('12/12/2006','12:23:04');
but it displays error at " ; "
can anyone help me to figure out the problem
Thanks a lot in advance.
Regards,
Sweety
What happens if you remove ";"?
|||hi,
thx for responding..i figured out the problem and i solved it..
bye
Sweety
.I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.
SELECT *
FROM table
WHERE column_name LIKE 'nvarchar%'
It works fine in when i type it in manually.
But im using a stored procedure from VS and it will not work with the '%' part
SELECT *
FROM table
WHERE column_name LIKE @.parameter%
Any help or ideas would be greatly appreciated.Hi, my similar line looks like ...
Dim myCommand = New SqlCommand("exec search_telephone '%" & filterValue1 & "%'", myConnection)
... where search_telephone is a stored procedure expecting an input of part of a surname.
NOTE the 2 percentage characters.
Richard|||Like uses a string as its input. So you'd need to use '%' + @.param + '%'|||Is your SQL running inside a stored proc? That's what it sounds like to me.
If that's the case, do something like this:
declare @.strSQL varchar(8000)
select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''' + @.parameter + ''''EXEC ( @.strSQL )
I do this all the time in my stored procs for searches. I haven't found another way to do this. The trick is getting the number of single quotes right.|||It should be:
declare @.strSQL varchar(8000)select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''%' + @.parameter + '%'''
EXEC ( @.strSQL )
but you get the point.
For help debugging these types of "dynamically generated" SQL statements, use PRINT ( @.strSQL ) and run it in query analyzer.|||in your stored procedure this should work and will not require SELECT permissions on the table like an EXEC(@.sql) would.
CREATE PROC [some_search]sql
@.Search nvarchar(50)
ASDeclare @.LikeSearch nvarchar(52)
-- you could also add do '%' + @.Search + '%' depending on how you want the search to work
SET @.LikeSearch = @.Search + '%'SELECT *
FROM table
WHERE column_name LIKE @.LikeSearch
@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30)
as
begin
declare @.balancefloat
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.SQL nvarchar(4000)
declare @.A float
declare @.Total float
select @.balance = balance, @.table = table, @.freetotal = freetotal
from info where number = @.number
SELECT @.SQL = 'select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
exec sp_executesql @.sql,N'@.Afloat output',@.Aoutput
set @.Total = @.balance / @.A + @.freetotal
end
return
but the server got nothing, then i wrote another Stored Procedure below:
create proc [MaxTime]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output,
@.balance float output,
@.A float output)
--here is the only modified i made
as
begin
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.SQL nvarchar(4000)
select @.balance = balance, @.table = table, @.freetotal = freetotal
from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table
+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)
+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +
\' ORDER BY LEN(code) DESC\'
exec sp_executesql
--@.sql,N\'@.Afloat output\',@.Aoutput --im not sure about this line
set @.Total = @.balance / @.A + @.freetotal
end
return
system error message：
Msg 170, Level 15, State 1, Procedure MaxTime, Line 11
Line 11: Incorrect syntax near \'@.Total \'.
Msg 137, Level 15, State 1, Procedure MaxTime,, Line 21
Must declare the variable \'@.Total \'.
Please help, appreciated
Hi,xxd
If you wanna get data from database without using dataset.
maybe you can try function.
By the below case, the SQL substring can't add the local varity into the sentance.
It was because the "exec sp_executesql " will be the other Transcation.
thanks for ur reply, as u mentioned about dataset, did you mean in SQL? or on the other server side?
and for the 'exec sp_executesql', it was only for excuting the dynamic@.sql to get those values that i need to calculate in set @.Total = @.balance / @.A + @.freetotal.
Thanks
|||
Hello xxd
as the requirement,I think you need to clac the value @.total return for Client that call sp [MAXTIME]
this is the sample code I wrote, try it. modi by your sample code.
if you need the detail for this. Let me know. :)
why "cursor"?
my target was get the return value From dynamic-SQLstring,
using the cursor delcare in global.
then fetch its content for out return value.
it's a better method.
Reference: Stored Procedure,Cursor;
Cheers,
Hunt
/* Sample Code by Hunt Begin*/
create proc [MaxTime]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output)
--here is the only modified i made
as
begin
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.SQL nvarchar(4000)
select @.balance = balance, @.table = table, @.freetotal = freetotal
from info where number = @.number
set @.sql =
' Declare tmpcur cursor for '
' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
exec (@.sql)
open tmpcur;
Fetch Next From tmpcur into @.A;
close tmpcur;
Deallocate tmpcur;
select @.total = @.balance / @.A + @.freetotal
/*Sample Code by Hunt End*/
1st, there are something wrong for this part below:
' Declare tmpcur cursor for '
' select @.a = a FROM'
please advise me that how to modify it, coz this is my 1st time to see write cursor this way :)
and for my case, i don't really need to use cursor, coz the ' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
will only back me one set of data. anyway.
based on your code, i modified mine as below:
alter proc [MaxTime]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output,
@.balance float output)
as
begin
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.SQL nvarchar(4000)
select @.balance = balance, @.table = table, @.freetotal = freetotal
from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table
+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)
+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +
\' ORDER BY LEN(code) DESC\'
exec sp_executesql
--don't know where did i get those \ from
set @.Total = @.balance / @.A + @.freetotal
end
return @.Total
return @.balance
however, got error message like:
Msg 201, Level 16, State 4, Procedure MaxTime, Line 0
Procedure 'MaxTime' expects parameter '@.Total', which was not supplied.
but one step closed i think
Cheers.
|||
ok,the important checkpoint on sys.procedure -> "exec sp_executesql"
check with my sample.
you'll get what you want.
Hint: as you set output varity with value,you shouldn't return any value. it's useless.
Reference with Books Online "sp_executesql"
Cheers,
Hunt
alter proc [sp_test1]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output,
@.balance float output)
as
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.A float
declare @.SQL nvarchar(4000);
declare @.SQLparm nvarchar(500);
set @.table ='car'
set @.balance = 3.0
set @.freetotal = 2.0
--the section below will be the most important.
set @.SQLparm = N'@.A float output'
select @.sql = ' select @.A = 2.0 FROM ' + @.table
exec sp_executesql @.sql,@.SQLparm ,@.A output
set @.Total = @.balance / @.A + @.freetotal
go
declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output
print ''
print @.tot
print @.free
go
however, why i need those below?
declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output
and i think it should be like exec [sp_test1] '1','2','3', @.Total output, @.balance output
otherwise, in fact, there are two clients are calling this procedure 1 of them was alright for getting the outputs, the other one doesn't, it is an application, in this application all i can do is to identify three input parameters,
which are @.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
and there are no more space for @.Total output and @.balance output.
so that when this application pass the exec command to sql server, it would be like exec [sp_test1] '1','2','3' rather than exec [sp_test1] '1','2','3', @.Total output, @.balance output
hope i explained clearly.
Cheers
|||
this thread got a little problem,i can't post any word on it.
try put default value behind the varity.
@.total float =0 out,@.balance float =0 out
in this sample,you can call sp with no output param.
check it.
Cheer.
|||hi thanks,?
or exec [sp_test1] '1','2','3','0','0'
it works fine when i am using exec [sp_test1] '1','2','3','0','0' for outputing values for the server (C++) or i do not even need to use the output value, it also works. However i still can not set the @.total float =0 out,@.balance float =0 out for the application case, and that application tells me that it did not get anything.
so is there any better way to solve it out?
many thx
|||
You have to put the default value when you create stored procedure.
as below
Create Proc [MAXTime] (@.numbera varchar(20),@.numberb varchar(20),@.numberc varchar(20),
@.total float =0 out,@.balance float =0 out);
after you alter the sp,you can call the proc by
exec [MAXTime] '1','2','3'
or
exec [MAXTime] '1','2','3',@.total out,@.balance out
try it.
thanks for that, by now i do not thin k the output will solve my problem, after all i realised that there are 3 types of output function of sproc:
1select @.something
2@.something output
3return@.something and return(0)
all of above are the same(of course not) or for some special using?
thanks
|||
I think I don't know what's your original requirement(or question).
maybe it could be describe more detail. On basiclly, the output Question seems like be sloved.
anyway,when we use stored procedure, it was defined for regular process.
and the return types that you said in last post were the normal method.
(I add the 4th as fire_trigger).
1. select @.something
2. @.something output
3. return@.something and return(0)
4. sometimes we also set it up as another type of trigger.
Best Regrads. :)
|||Hi HuntTsai:Thanks a lot.
In a statement I want to find the IDENTITY-column value for a row that
has the smallest value. I have tried this, but for the result i also
want to know the row_id for each. Can this be solved in a neat way,
without using temporary tables?
CREATE TABLE some_table
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,
row_value integer,
row_name varchar(30)
)
GO
/* DROP TABLE some_table */
insert into some_table (row_name, row_value) VALUES ('Alice', 0)
insert into some_table (row_name, row_value) VALUES ('Alice', 1)
insert into some_table (row_name, row_value) VALUES ('Alice', 2)
insert into some_table (row_name, row_value) VALUES ('Alice', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 2)
insert into some_table (row_name, row_value) VALUES ('Bob', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 4)
insert into some_table (row_name, row_value) VALUES ('Celine', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 6)
select min(row_value), row_name from some_table group by row_nameJon wrote:
> Hi all!
> In a statement I want to find the IDENTITY-column value for a row
that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?
> CREATE TABLE some_table
> (
> row_id INTEGER
> NOT NULL
> IDENTITY(1,1)
> PRIMARY KEY,
> row_value integer,
> row_name varchar(30)
> )
> GO
> /* DROP TABLE some_table */
> insert into some_table (row_name, row_value) VALUES ('Alice', 0)
> insert into some_table (row_name, row_value) VALUES ('Alice', 1)
> insert into some_table (row_name, row_value) VALUES ('Alice', 2)
> insert into some_table (row_name, row_value) VALUES ('Alice', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 2)
> insert into some_table (row_name, row_value) VALUES ('Bob', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 4)
> insert into some_table (row_name, row_value) VALUES ('Celine', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 6)
> select min(row_value), row_name from some_table group by row_name
*Assuming* that row_name/row_value combinations are unique, then it
would be:
select row_id,row_value,row_name from some_table t1 inner join (select
min(row_value) as row_value, row_name from some_table group by
row_name) t2 on t1.row_value = t2.row_value and t1.row_name =
t2.row_name
*Is* my assumption correct? If not, then there's some additional
grouping on the outer query, and a decision to be made on which row_id
to return (e.g. Min())|||Jon (jonsjostedt@.hotmail.com) writes:
> In a statement I want to find the IDENTITY-column value for a row that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?
Yes:
select s.row_id, x.min_value, x.row_name
from some_table s
join (select min_value = min(row_value), row_name
from some_table
group by row_name) as x on x.min_value = s.row_value
and x.row_name = s.row_name
What you see there is a *derived table*. A derived is sort of a temp
table within the query, but it is never materialized. In fact, the
optimizer may recast the computation order as long as this does not
affect the result.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Hi, I need to convert the values entered into textboxes by the users before I can insert into db.
have tried the following.
Dim eventnum As string 'tried using integer makes no difference what i declare the variable as
eventnum = Convert.ToInt32(txteventnum.Text)
This value needs to be inserted into the field event_number wich is datatype int
I get the following error message when I try to insert.
Exception Details:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'eventnum' to data type int.
Source Error:
Line 62:
Line 63: ' Execute(query)
Line 64: myCommand.ExecuteNonQuery()
Line 65:
Line 66: 'Close the connection
Stack Trace:
[SqlException (0x80131904): Conversion failed when converting the varchar value 'eventnum' to data type int.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
Request.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\loans\MemberPages\Request.aspx.vb:64
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
based on where your error is occurring, i suspect that the value from the textbox was successfully converted to an integer. This leads me to think that there's something wrong with how your query is constructed. Please post the code for constructing the query.
|||
thanks for the reply here is the code for query.
'Connection String value
Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString
'Create a SqlConnection instance
Using myConnection As New SqlConnection(conn)
myConnection.Open()
' Specify the SQL query
Const sql As String = "insert into requests ( [User_Name], [NHI], [Event_Number], [ACC_Number], [Request_Date]) values ('username','nhi','eventnum','accnum','reqstdate' )"
', [Required_Date], [NHI], [Event_Number], [ACC_Number], [Request_Date]
'Create a SqlCommand instance
Dim myCommand As New SqlCommand(sql, myConnection)
' Execute(query)
myCommand.ExecuteNonQuery()
'Close the connection
myConnection.Close()
End Using
resolved it was the query string
was
Const sql As String = "insert into requests ( [User_Name], [NHI],[Event_Number], [ACC_Number], [Request_Date]) values('username','nhi','eventnum','accnum','reqstdate' )"
now
Dim sql As String = "insert into requests ( [User_Name], [required_date],[NHI], [Event_Number], [ACC_Number], [Request_Date]) " & _
"values ('" & username & "','" & reqrddate & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & reqstdate & "' )"
But thanks for replying
|||glad you're all set.
But, please read up onSQL Injection Attacks
A parameterized query helps protect from sql injection and resolves issues related to quoting.
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/
Any help on this ? tnks in advance
Anyone ? It's a very urgent problem, my deadline is approaching and this problem remains. Do i have to replace the points by dot's ? It's the only solution ?
|||I don't know anything about locales, but I guess if I had this problem I'd read the amounts in as strings and then use a Derived Column component to do the necessary string manipulations and data conversion. Hopefully you don't have a lot of them or you have a convenient asynchronous component (like a Union) where you can drop out the string artifacts. Otherwise, you can do the transformation/conversion and drop the artifacts at the same time using an asynchronous script.
|||
What locale have you tried to use? Where did you set it? English (United States) should be available on your machine.
Thanks.
|||First of all tnks for your answers. Well the problem was indeed very simple, i was setting English as the locale and not English (United States), that's what i call a stupid error ;-). Anyway thank you very much for the help