|
Hi All
I am having sqlserver 2008R2 database, and in the table, there is a column "Text" with some string value.
Some of the strings are good (have the characters entered from keyboard" where some have values like "degree" symbol.
example:
Row 1 has string "Today temperature is 45Degrees C" where as Row 2 has string "Today temperature is 40°C
Row 2 has a symbol which is not entered through the keyboard. I want to filter such kind of symbols while querying.
Is there a way to query that way by filtering? If so, please provide me such query.
Thanks in advance,
Usha
Naina
|
|
|
|
|
That'd be rather slow; but you could loop through all chars, see if it falls within an expected range (say, ASCII upto 127), put that in a function, and call that from the query.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
There are multiple ways to do it. i would suggest you should review your data to identify how many other invalid characters you have after this just use the simple REPLACE() function to remove those. Furthermore, if you are planing to create a function for this then you can look into this Link.
hope it helps.
|
|
|
|
|
Hello,
The code below works fine with SQL Server 2008
But it seems to be deleted from newer versions of SQL Server.
How can change it so it works fine for all versions ?
Thank you
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sac_manager')
exec sp_droplogin 'sac_manager';
exec sp_addlogin @loginame = N'sac_manager', @passwd = N'', @defdb = N'SAC_DB';
USE [SAC_DB]
exec sp_adduser 'sac_manager'
exec sp_addrolemember 'db_owner', 'sac_manager';
|
|
|
|
|
Which part?
There is no separate set of commands that work on "all" versions. You'll have to find the part that is deprecated and find some replacement.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
hi everyone,
I have list of modules of a program in a asp.net repeater. I can change the order of the modules using drag and drop, but the matter is I want this order changes, apply in the database too!
There is a show order field in a table of the modules. I want to write a code in C# that after changing the order of modules in the repeater, I can submit the new order of the modules in the database by clicking a button called "Save the changes".
The problem is I can't access the repeater items in the event click of the button.
Please tell me how can I apply the changes in the database too.(C# code please)
Thanks a lot.
|
|
|
|
|
It all depends on the UI, aspx you would need to use javascript to get the modified sequence and pass it back to the server, that is what the asp forum is for.
For desktop or silverlight UI you use the underlying collection to determine the sequence and write that to the database.
This is not really a database problem but should have been asked in one of the UI forums for a better response.
And coming to a database forum and asking for c# code is rather silly!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a store procedure in my DB2 Database. It is just a simple insert statement. I have 10 columns in the table and each value is passed as a parameter to Store procedure and when I execute it throws an error
Overload resolution failed because no accessible 'Parameters' accepts this number of arguments.
My Store procedure is
CREATE PROCEDURE DEVSUSH.SP_CUSTINFO (
IN PRMCICID INTEGER ,
IN PRMCIFN VARCHAR(30) ,
IN PRMCILN VARCHAR(30) ,
IN PRMCIADDR VARCHAR(90) ,
IN PRMCICITY VARCHAR(20) ,
IN PRMCISTATE VARCHAR(20) ,
IN PRMCIZIP NUMERIC(9, 0) ,
IN PRMCIPHONE NUMERIC(10, 0) ,
IN PRMCIEMAIL VARCHAR(30) ,
IN PRMCICLDATE DATE )
LANGUAGE SQL
SPECIFIC DEVSUSH.SP_CUSTINFO
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
P1 : BEGIN
INSERT INTO DEVSUSH . CLCUSINF ( CICID , CIFN , CILN , CIADDR , CICITY , CISTATE , CIZIP , CIPHONE , CIEMAIL , CICLDATE ) VALUES ( @PRMCICID , @PRMCIFN , @PRMCILN , @PRMCIADDR , @PRMCICITY , @PRMCISTATE , @PRMCIZIP , @PRMCIPHONE , @PRMCIEMAIL , @PRMCICLDATE ) ;
END P1 ;
GRANT ALTER , EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO SUSHDEV ;
GRANT EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO VAIGROUP ;
How to solve the error?
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Can you show us the first few lines of the stored procedure?
The thing I am interested in seeing is everything from the name of the stored procedure to the closing parenthesis after the last parameter.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","SUSHDEV" ;
CREATE PROCEDURE DEVSUSH.SP_CUSTINFO (
IN PRMCICID INTEGER ,
IN PRMCIFN VARCHAR(30) ,
IN PRMCILN VARCHAR(30) ,
IN PRMCIADDR VARCHAR(90) ,
IN PRMCICITY VARCHAR(20) ,
IN PRMCISTATE VARCHAR(20) ,
IN PRMCIZIP NUMERIC(9, 0) ,
IN PRMCIPHONE NUMERIC(10, 0) ,
IN PRMCIEMAIL VARCHAR(30) ,
IN PRMCICLDATE DATE )
LANGUAGE SQL
SPECIFIC DEVSUSH.SP_CUSTINFO
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
P1 : BEGIN
INSERT INTO DEVSUSH . CLCUSINF ( CICID , CIFN , CILN , CIADDR , CICITY , CISTATE , CIZIP , CIPHONE , CIEMAIL , CICLDATE ) VALUES ( PRMCICID , PRMCIFN , PRMCILN , PRMCIADDR , PRMCICITY , PRMCISTATE , PRMCIZIP , PRMCIPHONE , PRMCIEMAIL , PRMCICLDATE ) ;
END P1 ;
GRANT ALTER , EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO SUSHDEV ;
GRANT EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO VAIGROUP ;
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Thanks - so it looks like there are 10 parameters for the stored procedure.
My next question is - have you checked that the code that is calling the stored procedure is only passing in 10 parameters(no more no less) and that the parameter names are exactly as they are in the stored procedure?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
well, I have 10 parameters and I am passing 10 parameters. Without names being correct in SP it wont insert data into table. But it is inserting the data in to table and still going to exception in catch block and throws an exception saying overload.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
weird thing is it is exceuting the Stored procedure and inserting data into table. after insertion it throws an exception saying overload parameters. How can solve this?
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Can you show me an example call to the stored procedure?
I would like to see what you call the parameters from the calling end.
Have you tried calling the stored procedure from an SQL client? If you have did, was the call successful?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Dim Cn As iDB2Connection = CreateConnection()
Try
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(@PrmCICID,@PrmCIFN,@PrmCILN,@PrmCIADDR,@PrmCICITY,@PrmCISTATE,@PrmCIZIP,@PrmCIPHONE,@PrmCIEMAIL,@PrmCICLDATE)", Cn)
strCIPHONE = strCIPHONE.Replace("-", "")
cm.Parameters.Add("@PRMCICID", iDB2DbType.iDB2Numeric).Value = Convert.ToInt32(strCICID)
cm.Parameters.Add("@PrmCIFN", iDB2DbType.iDB2VarChar).Value = strCIFN
cm.Parameters.Add("@PrmCILN", iDB2DbType.iDB2VarChar).Value = strCILN
cm.Parameters.Add("@PrmCIADDR", iDB2DbType.iDB2VarChar).Value = strCIADDR
cm.Parameters.Add("@PrmCICITY", iDB2DbType.iDB2VarChar).Value = strCICITY
cm.Parameters.Add("@PrmCISTATE", iDB2DbType.iDB2VarChar).Value = strCISTATE
cm.Parameters.Add("@PrmCIZIP", iDB2DbType.iDB2Numeric).Value = Convert.ToInt32(strCIZIP)
cm.Parameters.Add("@PrmCIPHONE", iDB2DbType.iDB2Numeric).Value = Convert.ToInt64(strCIPHONE)
cm.Parameters.Add("@PrmCIEMAIL", iDB2DbType.iDB2VarChar).Value = strCIEMAIL
cm.Parameters.Add("@PrmCICLDATE", iDB2DbType.iDB2Date).Value = strCICLDATE
cm.ExecuteNonQuery()
Catch ex As Exception
LogError.LogErrorIntoTextFile(ex, "InsertCust")
End Try
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Have you tried calling the stored procedure from an SQL client? If you have did, was the call successful?
I noticed that the stored procedure declaration does not contain @ in front of the parameter names after the procedure name, is this correct?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
If I remove @ in front of parameters, it throw an error saying no such parameter. If I keep it @ then it just inserts. I am using ibm series.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Judging by this:DB2 stored procedure examples[^] you don't need any @s so try removing them from the stored procedure and your call.
Disclaimer - I have never used DB2.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I removed from Stored procedure. It inserts the data into table but sill throws exception. But if I remove from my call in VB.NET code, it says no such parameter found and so no insertion of data.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Instead of this:
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(@PrmCICID,@PrmCIFN,@PrmCILN,@PrmCIADDR,@PrmCICITY,@PrmCISTATE,@PrmCIZIP,@PrmCIPHONE,@PrmCIEMAIL,@PrmCICLDATE)", Cn)
Try this:
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(?,?,?,?,?,?,?,?,?,?)", Cn)
and read this[^]
[edit]
? added
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I tried the one which you said and Now this is throwing
SQL0440 Routine QZ9AFC1C3BBACE2001 in not found with specified parameters.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
sudevsu wrote: I tried the one which you said and Now this is throwing
The one with the "?" question marks for parameters?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
with ?
I get this error
SQL0104 Token PRMCICID was not valid. Valid tokens: ) ,.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Read this[^] article - I think it should help.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Thanks Guy. I wish this should resolve ASAP. It is taking my whole lot of time. 
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
