I populated a parameter list with dataset.
The parameter @.type_year has items look like
"plan_2000" or "Actual_2000".
Now my question is, if we can use some string function targeting @.type_year in the query.
something like "select ..... from ... where
year=getyear(@.type_year) and type=gettype(@.type_year)" ?
Please let me know if you know the feasibility. Thanks.
Sincerelyyou can. regarding your SQL database its easier to do.
for example in SQL Serbver, you can create 2 functions to extract the year
part or the type part of your parameter. (using the SQL substring function)
but I recommand to use 2 parameters
* 1 for the year
* 1 for the type
"Frank RS" <FrankRS@.discussions.microsoft.com> a écrit dans le message de
news:2B0E0EA7-2955-4B9A-BEFA-053ECA609CB9@.microsoft.com...
> I populated a parameter list with dataset.
> The parameter @.type_year has items look like
> "plan_2000" or "Actual_2000".
> Now my question is, if we can use some string function targeting
@.type_year in the query.
> something like "select ..... from ... where
> year=getyear(@.type_year) and type=gettype(@.type_year)" ?
> Please let me know if you know the feasibility. Thanks.
>
> Sincerely
Showing posts with label string. Show all posts
Showing posts with label string. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
can varchar(max) store images in sql ?
If you serialize the image into a string ? If you want to save a byte array to a string, do you have to use a base64 encoded string ?
Tuesday, March 20, 2012
can this sql work correctly?
I want to get the records from table2 and table1 that table2.id2(string) include table1.id(string),means table1.id is a substring in table2.id2,can this sql work correctly? Thanks!
SELECT table1.abc, table1.id, table2.id2, table2.ddd
FROM table2 INNER JOIN table1 ON table2.id2 like table1.idI am sure this will work.
SQL> select * from a;
C1 C2
---- -------
AAAA santosh sarkar
AAAB debasish datta
AAAC sujata agarwal
AAAD rina ray
SQL> select * from b
2 ;
C3 C4
---- -------
XXXX santosh
XXXY debasish
XXXZ agarwa
SQL>
select a.*, b.*
from a, b
where substr(a.C2, instr(a.c2,b.c4), length(b.c4)) = b.c4
SQL> /
C1 C2 C3 C4
---- ------- ---- -------
AAAA santosh sarkar XXXX santosh
AAAB debasish datta XXXY debasish
AAAC sujata agarwal XXXZ agarwa
SQL>
Santosh Sarkar|||tshm, you almost got the query right! You only forgot to put the wildcards. This will return all rows where table1.id is found in table2.id2.
SELECT table1.abc, table1.id, table2.id2, table2.ddd
FROM table2 INNER JOIN table1 ON table2.id2 like '%'+table1.id+'%'
SELECT table1.abc, table1.id, table2.id2, table2.ddd
FROM table2 INNER JOIN table1 ON table2.id2 like table1.idI am sure this will work.
SQL> select * from a;
C1 C2
---- -------
AAAA santosh sarkar
AAAB debasish datta
AAAC sujata agarwal
AAAD rina ray
SQL> select * from b
2 ;
C3 C4
---- -------
XXXX santosh
XXXY debasish
XXXZ agarwa
SQL>
select a.*, b.*
from a, b
where substr(a.C2, instr(a.c2,b.c4), length(b.c4)) = b.c4
SQL> /
C1 C2 C3 C4
---- ------- ---- -------
AAAA santosh sarkar XXXX santosh
AAAB debasish datta XXXY debasish
AAAC sujata agarwal XXXZ agarwa
SQL>
Santosh Sarkar|||tshm, you almost got the query right! You only forgot to put the wildcards. This will return all rows where table1.id is found in table2.id2.
SELECT table1.abc, table1.id, table2.id2, table2.ddd
FROM table2 INNER JOIN table1 ON table2.id2 like '%'+table1.id+'%'
Thursday, March 8, 2012
Can SQL Server do this?
I was curious.
I am building a filter string on a webpage to pass to a stored
procedure. The filter string would be some kind of IN clause.
Is there a way to pass the IN clause to the stored procedure?
For instance, let's say you have a stored procedure called MyProc
It takes 1 parameter, maybe called @.alpha, potentially a string like
"('CA','MA')"
Is there a way that you could say something like:
WHERE @.alpha
In other words, pass the filter string directly to the SP? I tried it
and couldn't get it to work and I was wondering if there were a way to
even do this so I can keep the query as a stored procedure.Hi Brent
Before we start I am hoping that you are aware with code injection issues
that the dynamic creation of SQL statements can lead to. I advise strongly
against building dynamic queries as such because the security implications
are enormous.
I would possible prepopulate a temp table such as the following.
CREATE TABLE #criteria ( lookup varchar(50))
INSERT #criteria (lookup) VALUES("John")
INSERT #criteria (lookup) VALUES("Peter")
INSERT #criteria (lookup) VALUES("Alan")
Then call the procedure
EXEC sp_lookup
With will do something like
SELECT somefield FROM sometable s where EXISTS ( SELECT * FROM #criteria
WHERE s.somelookupfield = #criteria.lookup)
This may require you to recode some of the page but it is safer, as long of
course as you validate the Web form input first so it doen't get injectect
into the insert statements.
Hope this helps
Phil
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>|||The only way you can do that is to use dynamic sql. This can introduce
security concerns, so you should be careful about using it. There have been
many posts on this newsgroup about dynamic sql--some with links to very good
information, so I suggest you review them and consider all of the
ramifications before implementing it.
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>|||I personally prefer this method:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
...if you need it in pure T-SQL.
ML|||I use a method described here:
http://www.sommarskog.se/arrays-in-sql.html
In the table of contents, click on "List-of-strings".
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>
I am building a filter string on a webpage to pass to a stored
procedure. The filter string would be some kind of IN clause.
Is there a way to pass the IN clause to the stored procedure?
For instance, let's say you have a stored procedure called MyProc
It takes 1 parameter, maybe called @.alpha, potentially a string like
"('CA','MA')"
Is there a way that you could say something like:
WHERE @.alpha
In other words, pass the filter string directly to the SP? I tried it
and couldn't get it to work and I was wondering if there were a way to
even do this so I can keep the query as a stored procedure.Hi Brent
Before we start I am hoping that you are aware with code injection issues
that the dynamic creation of SQL statements can lead to. I advise strongly
against building dynamic queries as such because the security implications
are enormous.
I would possible prepopulate a temp table such as the following.
CREATE TABLE #criteria ( lookup varchar(50))
INSERT #criteria (lookup) VALUES("John")
INSERT #criteria (lookup) VALUES("Peter")
INSERT #criteria (lookup) VALUES("Alan")
Then call the procedure
EXEC sp_lookup
With will do something like
SELECT somefield FROM sometable s where EXISTS ( SELECT * FROM #criteria
WHERE s.somelookupfield = #criteria.lookup)
This may require you to recode some of the page but it is safer, as long of
course as you validate the Web form input first so it doen't get injectect
into the insert statements.
Hope this helps
Phil
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>|||The only way you can do that is to use dynamic sql. This can introduce
security concerns, so you should be careful about using it. There have been
many posts on this newsgroup about dynamic sql--some with links to very good
information, so I suggest you review them and consider all of the
ramifications before implementing it.
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>|||I personally prefer this method:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
...if you need it in pure T-SQL.
ML|||I use a method described here:
http://www.sommarskog.se/arrays-in-sql.html
In the table of contents, click on "List-of-strings".
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>
Wednesday, March 7, 2012
Can SQL break an array into one string? [Stored Procedure]
Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like
SELECT * FROM [eai.Documents] WHERECategoryId=11 ORCategoryId=16 ORCategoryId=18.
My question is: Can I do the same in my stored procedure?
Here is it:
set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[eai.GetSubCategoriesById](@.Idint)ASdeclare @.pathvarchar(100);SELECT @.path=PathFROM [eai.FileCategories]WHERE Id = @.Id;SELECT Id, ParentCategoryId,Name, NumActiveAdsFROM [eai.FileCategories]WHERE PathLIKE @.Path +'%'ORDER BY Path
Thank you
Artashes
There is no Array in Stored procedure, but you do can it by use dynamic sql . pass stored procedure a string as 11,16,18
then
in store procedure do
exec N'select * from yourTable where id in ' + @.idlist
Hope this help
|||DavidDu thank you for answer. I found another solution.
It is herehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=475225&SiteID=1
Artashes
Saturday, February 25, 2012
can somone help me tell what is wrong with this coding?
GLOBAL STRING Line1_BatchID
GLOBAL STRING Line1_CompletedDate
GLOBAL INT Line1_CompletedQty
GLOBAL STRING Line1_CompletedTime
GLOBAL STRING Line1_ExpiryDate
GLOBAL INT Line1_Qty
GLOBAL STRING Line1_OrderID
GLOBAL INT Line1_BatchID_ID
GLOBAL INT Line1_CompletedDate_ID
GLOBAL INT Line1_CompletedQty_ID
GLOBAL INT Line1_CompletedTime_ID
GLOBAL INT Line1_ExpiryDate_ID
GLOBAL INT Line1_OrderStatus_ID
GLOBAL STRING Line2_BatchID
GLOBAL STRING Line2_CompletedDate
GLOBAL INT Line2_CompletedQty
GLOBAL STRING Line2_CompletedTime
GLOBAL STRING Line2_ExpiryDate
GLOBAL STRING Line2_OrderStatus
GLOBAL INT Line2_Qty
GLOBAL STRING Line2_OrderID
GLOBAL INT Line2_BatchID_ID
GLOBAL INT Line2_CompletedDate_ID
GLOBAL INT Line2_CompletedQty_ID
GLOBAL INT Line2_CompletedTime_ID
GLOBAL INT Line2_ExpiryDate_ID
GLOBAL INT Line2_OrderStatus_ID
GLOBAL STRING Line3_BatchID
GLOBAL STRING Line3_CompletedDate
GLOBAL INT Line3_CompletedQty
GLOBAL STRING Line3_CompletedTime
GLOBAL STRING Line3_ExpiryDate
GLOBAL STRING Line3_OrderStatus
GLOBAL INT Line3_Qty
GLOBAL STRING Line3_OrderID
GLOBAL INT Line3_BatchID_ID
GLOBAL INT Line3_CompletedDate_ID
GLOBAL INT Line3_CompletedQty_ID
GLOBAL INT Line3_CompletedTime_ID
GLOBAL INT Line3_ExpiryDate_ID
GLOBAL INT Line3_OrderStatus_ID
GLOBAL STRING result
GLOBAL STRING result2
FUNCTION getOrderDetail(STRING Product_ID)
INT counter = 0
INT statusSQL, sqlResult;
STRING Sql1
STRING Sql2
STRING Sql3
result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
//Select first SetId
result = SQLGetField(statusSQL, "SetId")
END
END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter < 9 THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END
END
IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END
IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END
counter = counter + 1
END
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
FUNCTION UpdateBatchID(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID)
END
sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
FUNCTION UpdateCompletedQty(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID)
END
sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
FUNCTION UpdateCompletedOrder(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedTime + "' WHERE [Id] = " + IntToStr(Line1_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line1_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line1_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedDate + "' WHERE [Id] = " + IntToStr(Line2_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedTime + "' WHERE [Id] = " + IntToStr(Line2_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line2_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line2_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedDate + "' WHERE [Id] = " + IntToStr(Line3_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedTime + "' WHERE [Id] = " + IntToStr(Line3_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line3_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
INT FUNCTION getLine1_Qty()
RETURN Line1_Qty
END
INT FUNCTION getLine1_CompletedQty()
RETURN Line1_CompletedQty
END
STRING FUNCTION getLine1_OrderID()
RETURN Line1_OrderID
END
STRING FUNCTION getLine1_BatchID()
RETURN Line1_BatchID
END
INT FUNCTION getLine2_Qty()
RETURN Line2_Qty
END
INT FUNCTION getLine2_CompletedQty()
RETURN Line2_CompletedQty
END
STRING FUNCTION getLine2_OrderID()
RETURN Line2_OrderID
END
STRING FUNCTION getLine2_BatchID()
RETURN Line2_BatchID
END
INT FUNCTION getLine3_Qty()
RETURN Line3_Qty
END
INT FUNCTION getLine3_CompletedQty()
RETURN Line3_CompletedQty
END
STRING FUNCTION getLine3_OrderID()
RETURN Line3_OrderID
END
STRING FUNCTION getLine3_BatchID()
RETURN Line3_BatchID
END
FUNCTION dbTest()
INT statusSQL, sqlResult;
INT counter = 0
STRING Sql1
STRING Sql2
STRING Sql3
STRING Product_ID = "Biscuit"
result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
result = SQLGetField(statusSQL, "SetId")
END
END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
Line1_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END
IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
Line2_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END
IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
Line3_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END
counter = counter + 1
END
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
STRING FUNCTION getresult()
RETURN result
END
STRING FUNCTION getresult2()
RETURN result2
END
Give us some clues. What do you think may be the problem?
Most of us are not going to spend much time looking through your code without a hint about what we are looking for.
And it appears to be VB5 (or earlier) code -GLOBAL variables have long fallen by the wayside.
|||i have some field in the SQL table, when information is passed in, the data goes into the wrong field. for eg, i have the field date and quantity, when information is passed into the SQL, the data got mixed up, date data is stored into the quantity field and quantity is stored in to the date field....Friday, February 24, 2012
Can someone help with this T-SQL Real quick?
Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:
(@.StudioINT =NULL,@.Br1INT =NULL,@.Br2INT =NULL,@.Br3INT =NULL,@.Br4INT =NULL,@.OverBr4INT =NULL,@.CondoINT =NULL,@.ListingTypevarchar(10) =NULL,@.WindowAirINT =NULL,@.CentralACINT =NULL,@.BalconyDeckPatioINT =NULL,@.UseOfYardINT =NULL,@.DishwasherINT =NULL,@.WasherDryerINT =NULL,@.FireplaceINT =NULL,@.EIKINT =NULL,@.HardwoodFloorsINT =NULL,@.BroadbandNetINT =NULL,@.TVINT =NULL,@.ThermostatINT =NULL,@.LandlordNotPresentINT =NULL,@.SmokingINT =NULL,@.NoPetsAllowedINT =NULL,@.CatINT =NULL,@.MoreCatsINT =NULL,@.SmallDogINT =NULL,@.LargeDogsINT =NULL,@.DoorpersonINT =NULL,@.IngroundPoolINT =NULL,@.AboveGroundPoolINT =NULL,@.ElevatorINT =NULL,@.UseOfGarageINT =NULL,@.LaundryFacilitiesINT =NULL,@.HealthCenterINT =NULL,@.StorageAreasINT =NULL,@.WheelchairAccessINT =NULL,@.BusinessCentersINT =NULL,@.RentChargeMinINT =NULL,@.RentChargeMaxINT =NULL,@.DebugBIT = 1)AS SET NOCOUNT ONDECLARE @.SQLVARCHAR(8000)SET @.SQL ='SELECTr.REListingID,r.REListingDate,r.Username,r.ZipCode,r.ListingType,r.StudioFlag,r.BRFlag1,r.BRFlag2,r.BRFlag3,r.BRFlag4,r.OverBRFlag4,r.CondoFlag,a.WindowAir,a.CentralAir,a.BalconyDeckPatio,a.UseOfYard,a.Dishwasher,a.WasherDryer,a.Fireplace,a.EIK,a.HardwoodFloors,a.BroadbandNet,a.TV,a.Thermostat,a.LandlordNotPresent,a.Smoking,a.NoPetsAllowed,a.Cat,a.MoreCats,a.SmallDog,a.LargeDogs,a.Doorperson,a.IngroundPool,a.AboveGroundPool,a.Elevator,a.UseOfGarage,a.LaundryFacilities,a.HealthCenter,a.StorageAreas,a.WheelchairAccess,a.BusinessCenters,a.RentCharge,a.RentFrequencyFROMdb_REListings as rINNER JOINdb_RentalAmenities AS a ON a.REListingID = r.REListingIDWHERE1 = 1'IF @.StudioISNOT NULLSET @.SQL = @.SQL +' AND r.StudioFlag = ' +CONVERT(VARCHAR(20), @.Studio)IF @.Br1ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag1 = ' +CONVERT(VARCHAR(20), @.Br1)IF @.Br2ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag2 = ' +CONVERT(VARCHAR(20), @.Br2)IF @.Br3ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag3 = ' +CONVERT(VARCHAR(20), @.Br3)IF @.Br4ISNOT NULLSET @.SQL = @.SQL +' AND r.BRFlag4 = ' +CONVERT(VARCHAR(20), @.Br4)IF @.OverBr4ISNOT NULLSET @.SQL = @.SQL +' AND r.OverBRFlag4 = ' +CONVERT(VARCHAR(20), @.OverBr4)IF @.CondoISNOT NULLSET @.SQL = @.SQL +' AND r.CondoFlag = ' +CONVERT(VARCHAR(20), @.Condo)IF @.ListingTypeISNOT NULLSET @.SQL = @.SQL +' AND r.ListingType = ' +CONVERT(char, @.ListingType)IF @.WindowAirISNOT NULLSET @.SQL = @.SQL +' AND a.WindowAir = ' +CONVERT(VARCHAR(20), @.WindowAir)IF @.CentralACISNOT NULLSET @.SQL = @.SQL +' AND a.CentralAir = ' +CONVERT(VARCHAR(20), @.CentralAC)IF @.BalconyDeckPatioISNOT NULLSET @.SQL = @.SQL +' AND a.BalconyDeckPatio = ' +CONVERT(VARCHAR(20), @.BalconyDeckPatio)IF @.UseOfYardISNOT NULLSET @.SQL = @.SQL +' AND a.UseOfYard = ' +CONVERT(VARCHAR(20), @.UseOfYard)IF @.DishwasherISNOT NULLSET @.SQL = @.SQL +' AND a.Dishwasher = ' +CONVERT(VARCHAR(20), @.Dishwasher)IF @.WasherDryerISNOT NULLSET @.SQL = @.SQL +' AND a.WasherDryer = ' +CONVERT(VARCHAR(20), @.WasherDryer)IF @.FireplaceISNOT NULLSET @.SQL = @.SQL +' AND a.Fireplace = ' +CONVERT(VARCHAR(20), @.Fireplace)IF @.EIKISNOT NULLSET @.SQL = @.SQL +' AND a.EIK = ' +CONVERT(VARCHAR(20), @.EIK)IF @.HardwoodFloorsISNOT NULLSET @.SQL = @.SQL +' AND a.HardwoodFloors = ' +CONVERT(VARCHAR(20), @.HardwoodFloors)IF @.BroadBandNetISNOT NULLSET @.SQL = @.SQL +' AND a.BroadbandNet = ' +CONVERT(VARCHAR(20), @.BroadbandNet)IF @.TVISNOT NULLSET @.SQL = @.SQL +' AND a.TV = ' +CONVERT(VARCHAR(20), @.TV)IF @.ThermostatISNOT NULLSET @.SQL = @.SQL +' AND a.Thermostat = ' +CONVERT(VARCHAR(20), @.Thermostat)IF @.LandlordNotPresentISNOT NULLSET @.SQL = @.SQL +' AND a.LandLordNotPresent = ' +CONVERT(VARCHAR(20), @.LandLordNotPresent)IF @.SmokingISNOT NULLSET @.SQL = @.SQL +' AND a.Smoking = ' +CONVERT(VARCHAR(20), @.Smoking)IF @.NoPetsAllowedISNOT NULLSET @.SQL = @.SQL +' AND a.NoPetsAllowed = ' +CONVERT(VARCHAR(20), @.NoPetsAllowed)IF @.CatISNOT NULLSET @.SQL = @.SQL +' AND a.Cat = ' +CONVERT(VARCHAR(20), @.Cat)IF @.MoreCatsISNOT NULLSET @.SQL = @.SQL +' AND a.MoreCats = ' +CONVERT(VARCHAR(20), @.MoreCats)IF @.SmallDogISNOT NULLSET @.SQL = @.SQL +' AND a.SmallDog = ' +CONVERT(VARCHAR(20), @.SmallDog)IF @.LargeDogsISNOT NULLSET @.SQL = @.SQL +' AND a.LargeDogs = ' +CONVERT(VARCHAR(20), @.LargeDogs)IF @.DoorpersonISNOT NULLSET @.SQL = @.SQL +' AND a.Doorperson = ' +CONVERT(VARCHAR(20), @.Doorperson)IF @.IngroundPoolISNOT NULLSET @.SQL = @.SQL +' AND a.IngroundPool = ' +CONVERT(VARCHAR(20), @.IngroundPool)IF @.AboveGroundPoolISNOT NULLSET @.SQL = @.SQL +' AND a.AboveGroundPool = ' +CONVERT(VARCHAR(20), @.AboveGroundPool)IF @.ElevatorISNOT NULLSET @.SQL = @.SQL +' AND a.Elevator = ' +CONVERT(VARCHAR(20), @.Elevator)IF @.UseOfGarageISNOT NULLSET @.SQL = @.SQL +' AND a.UseOfGarage = ' +CONVERT(VARCHAR(20), @.UseOfGarage)IF @.LaundryFacilitiesISNOT NULLSET @.SQL = @.SQL +' AND a.LaundryFacilities = ' +CONVERT(VARCHAR(20), @.LaundryFacilities)IF @.HealthCenterISNOT NULLSET @.SQL = @.SQL +' AND a.Health Center = ' +CONVERT(VARCHAR(20), @.HealthCenter)IF @.StorageAreasISNOT NULLSET @.SQL = @.SQL +' AND a.StorageAreas = ' +CONVERT(VARCHAR(20), @.StorageAreas)IF @.WheelchairAccessISNOT NULLSET @.SQL = @.SQL +' AND a.WheelchairAccess = ' +CONVERT(VARCHAR(20), @.WheelchairAccess)IF @.BusinessCentersISNOT NULLSET @.SQL = @.SQL +' AND a.BusinessCenters = ' +CONVERT(VARCHAR(20), @.BusinessCenters)IF @.RentChargeMinISNOT NULL AND @.RentChargeMAXISNOT NULLSET @.SQL = @.SQL +' AND a.RentCharge BETWEEN ' +CONVERT(VARCHAR(20), @.RentChargeMin) +' AND ' +CONVERT(VARCHAR(20), @.RentChargeMax)IF @.RentChargeMinISNOT NULL AND @.RentChargeMAXISNULLSET @.SQL = @.SQL +' AND a.RentCharge >= ' +CONVERT(VARCHAR(20), @.RentChargeMin)IF @.RentChargeMAXISNULL AND @.RentChargeMAXISNOT NULLSET @.SQL = @.SQL +' AND a.RentCharge <= ' +CONVERT(VARCHAR(20), @.RentChargeMax)IF @.Debug = 1PRINT @.SQLEXEC (@.SQL)
Try this:
@.StudioINT =NULL,
@.Br1INT =NULL,
@.Br2INT =NULL,
@.Br3INT =NULL,
@.Br4INT =NULL,
@.OverBr4INT =NULL,
@.CondoINT =NULL,
@.ListingTypevarchar(10) =NULL,
@.WindowAirINT =NULL,
@.CentralACINT =NULL,
@.BalconyDeckPatioINT =NULL,
@.UseOfYardINT =NULL,
@.DishwasherINT =NULL,
@.WasherDryerINT =NULL,
@.FireplaceINT =NULL,
@.EIKINT =NULL,
@.HardwoodFloorsINT =NULL,
@.BroadbandNetINT =NULL,
@.TVINT =NULL,
@.ThermostatINT =NULL,
@.LandlordNotPresentINT =NULL,
@.SmokingINT =NULL,
@.NoPetsAllowedINT =NULL,
@.CatINT =NULL,
@.MoreCatsINT =NULL,
@.SmallDogINT =NULL,
@.LargeDogsINT =NULL,
@.DoorpersonINT =NULL,
@.IngroundPoolINT =NULL,
@.AboveGroundPoolINT =NULL,
@.ElevatorINT =NULL,
@.UseOfGarageINT =NULL,
@.LaundryFacilitiesINT =NULL,
@.HealthCenterINT =NULL,
@.StorageAreasINT =NULL,
@.WheelchairAccessINT =NULL,
@.BusinessCentersINT =NULL,
@.RentChargeMinINT =NULL,
@.RentChargeMaxINT =NULL,
@.DebugBIT = 1
)
AS
SET NOCOUNT ON
SELECT
r.REListingID,
r.REListingDate,
r.Username,
r.ZipCode,
r.ListingType,
r.StudioFlag,
r.BRFlag1,
r.BRFlag2,
r.BRFlag3,
r.BRFlag4,
r.OverBRFlag4,
r.CondoFlag,
a.WindowAir,
a.CentralAir,
a.BalconyDeckPatio,
a.UseOfYard,
a.Dishwasher,
a.WasherDryer,
a.Fireplace,
a.EIK,
a.HardwoodFloors,
a.BroadbandNet,
a.TV,
a.Thermostat,
a.LandlordNotPresent,
a.Smoking,
a.NoPetsAllowed,
a.Cat,
a.MoreCats,
a.SmallDog,
a.LargeDogs,
a.Doorperson,
a.IngroundPool,
a.AboveGroundPool,
a.Elevator,
a.UseOfGarage,
a.LaundryFacilities,
a.HealthCenter,
a.StorageAreas,
a.WheelchairAccess,
a.BusinessCenters,
a.RentCharge,
a.RentFrequency
FROM db_REListings as r
INNER JOIN db_RentalAmenities AS a ON a.REListingID = r.REListingID
WHERE (@.StudioIS NULL OR r.StudioFlag =@.Studio)
AND (@.Br1IS NULL OR r.BRFlag1 = @.Br1)
...
AND (@.ListingType IS NULL OR r.ListingType =@.ListingType)
...
etc...
but the IF's are there to match conditional options... how would (@.ListingType IS NULL OR r.ListingType =@.ListingType) make that work still?
|||Nevermind... the variable needed to have single quotes around it..
IF @.ListingType IS NOT NULL
SET @.SQL = @.SQL + ' AND r.ListingType = ''' + CONVERT(VARCHAR(20), @.ListingType) + ''''
Mnemonic:
but the IF's are there to match conditional options... how would (@.ListingType IS NULL OR r.ListingType =@.ListingType) make that work still?
If ListingType equals null then
(@.ListingType IS NULL OR r.ListingType =@.ListingType) is also true! This is just another way of handling optional parameters.
So yes, the missing quotes are causing your error, but the solution that I wrote will also work (did you try it?) and I believe it is the better solution. Another advantage of using parameters is that you don't have to worry about wheter or not to use quotes...
|||Oh... reading the logic you're definitly right...
I went my way with the query, but i might switch it over to yours... because the select statement is inside of @.sql (i think) none of my schema is showing any columns on my gridview...
Any idea on why that would do that? I would really rather not re-write this query (for the 3rd time)
Thursday, February 16, 2012
can Profiler display port number?
Where - in SQL Server Profiler - can I see the port number that someone has used to connect to a database?
e.g. given the connect string "tcp:MACHINE1\INSTANCE7,3045" - where in Profiler does it tell me that the connection is using port 3045? I looked at "audit login" and "existing connection" but I don't see a port number...
Any other SQL/Windows tools that I can use to monitor connections to databases on specific ports?
thanks
Hi,
open ports can be monitored using the commandline tool netstat -a.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Tuesday, February 14, 2012
can openxml write multiple fields - 1 row?
I am starting to get the hang of this. The trick is in
the ConvertToXML UDF. Here is the xml string that I want
to create which will work with openxml
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
Here is the string that I pass in to the ConvertToXML UDF
'1,Joe,Smith,1/1/01,3'
Here is the Original function
CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
@.Delim varchar (5))
returns varchar (8000)
as
begin
return ('<ROOT><Worktable Value="' + replace (@.InputStr,
@.Delim, '"/><Worktable Value="') + '"/></ROOT>')
end
which yields the following from '1,Joe,Smith,1/1/01,3'
<ROOT><Worktable Value="1"/><Worktable
Value="Joe"/><Worktable Value="Smith"/><Worktable
Value="1/1/01"/><Worktable Value="3"/></ROOT>
which is 5 rows and one column
But I need it to look like this:
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
to get 1 row and 5 columns
so in the function my kludge would look like this:
...
declare @.rownum int
declare @.fName varchar(10)
declare @.lName varchar(10)
declare @.dob datetime
declare @.age int
declare @.pos1 int
declare @.pos2 int
begin
--@.input = '1,Joe,Smith,1/1/01,3'
set @.rownum = Left(@.input, 1)
set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
comma so @.pos1 is at 2nd comma
set @.fName = substring(@.input, 3, @.pos1 - 3)
set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
+ @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
+ @.age + '/><ROOT>
End
If anyone has a suggestion how I could streamline this -
please share because my actual row is more like 50
fields. I am thinking a loop would be more efficient, but
I don't know how to implement a loop in a Sql Server UDF.
Could someone share please?
Thanks,
Ed
set @.pos1 = charindex(@.input, ',', @.pos2) --
Hi Ed,
You might have a slightly easier time converting the input string to XML
using C#/VB (or some other mid-tier programming language). Also from a DB
performance standpoint offloading all this string manipulation elsewhere may
help (although the XML you send over the wire will be bigger) - of course
all this depends on how your app is set up and where your bottlenecks are,
so take this with a grain of salt
You can check out
http://msdn.microsoft.com/library/de...chtutorial.asp
for a good example of implementing a class that supports the foreach loop
construct over an array of string tokens. Combine that with your favorite
TextReader implementation to read through all the "rows" of string tokens to
generate your XML. In terms of generating the XML you can just use string
manipulation or the XmlWriter API
(http://msdn.microsoft.com/library/de...-us/cpref/html
/frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
getting used to but it gives you a higher level abstraction write access to
the underlying XML.
You could loop through your string tokens and create XML that looks like:
<Root value1='{somevalue}' value2='{someothervalue}...</Root> by appending
the iteration number to the name of the attribute value - if there is a
consistent ordering of fields in the file you could then just use the
"colPattern" construct in OPENXML to map the attributes to the appropriate
columns in the table.
If you need to do the whole thing in the server let me know and we can think
about further.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"ED" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
> I am starting to get the hang of this. The trick is in
> the ConvertToXML UDF. Here is the xml string that I want
> to create which will work with openxml
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> Here is the string that I pass in to the ConvertToXML UDF
> '1,Joe,Smith,1/1/01,3'
> Here is the Original function
> ----
> CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
> @.Delim varchar (5))
> returns varchar (8000)
> as
> begin
> return ('<ROOT><Worktable Value="' + replace (@.InputStr,
> @.Delim, '"/><Worktable Value="') + '"/></ROOT>')
> end
> which yields the following from '1,Joe,Smith,1/1/01,3'
> <ROOT><Worktable Value="1"/><Worktable
> Value="Joe"/><Worktable Value="Smith"/><Worktable
> Value="1/1/01"/><Worktable Value="3"/></ROOT>
> which is 5 rows and one column
> ----
> But I need it to look like this:
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> to get 1 row and 5 columns
> so in the function my kludge would look like this:
> ...
> declare @.rownum int
> declare @.fName varchar(10)
> declare @.lName varchar(10)
> declare @.dob datetime
> declare @.age int
> declare @.pos1 int
> declare @.pos2 int
> begin
> --@.input = '1,Joe,Smith,1/1/01,3'
> set @.rownum = Left(@.input, 1)
> set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
> comma so @.pos1 is at 2nd comma
> set @.fName = substring(@.input, 3, @.pos1 - 3)
> set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
> set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
> set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
> set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
> set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
> set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
> set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
> return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
> + @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
> + @.age + '/><ROOT>
> End
> If anyone has a suggestion how I could streamline this -
> please share because my actual row is more like 50
> fields. I am thinking a loop would be more efficient, but
> I don't know how to implement a loop in a Sql Server UDF.
> Could someone share please?
> Thanks,
> Ed
> set @.pos1 = charindex(@.input, ',', @.pos2) --
>
>
|||Actually I was just looking around MSDN and if you can do it using C# then
you will have a much easier time with Chris Lovett's XmlCsvReader at
http://msdn.microsoft.com/library/de...lcsvreader.asp -
This will give you a much easier time - just put the column names you want
in the first row of your CSV file/stream and this thing will do the work for
you. The link has a code sample to get you going.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi Ed,
> You might have a slightly easier time converting the input string to XML
> using C#/VB (or some other mid-tier programming language). Also from a DB
> performance standpoint offloading all this string manipulation elsewhere
may
> help (although the XML you send over the wire will be bigger) - of course
> all this depends on how your app is set up and where your bottlenecks are,
> so take this with a grain of salt
> You can check out
>
http://msdn.microsoft.com/library/de...chtutorial.asp
> for a good example of implementing a class that supports the foreach loop
> construct over an array of string tokens. Combine that with your favorite
> TextReader implementation to read through all the "rows" of string tokens
to
> generate your XML. In terms of generating the XML you can just use string
> manipulation or the XmlWriter API
>
(http://msdn.microsoft.com/library/de...-us/cpref/html
> /frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
> getting used to but it gives you a higher level abstraction write access
to
> the underlying XML.
> You could loop through your string tokens and create XML that looks like:
> <Root value1='{somevalue}' value2='{someothervalue}...</Root> by
appending
> the iteration number to the name of the attribute value - if there is a
> consistent ordering of fields in the file you could then just use the
> "colPattern" construct in OPENXML to map the attributes to the appropriate
> columns in the table.
> If you need to do the whole thing in the server let me know and we can
think
> about further.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ED" <anonymous@.discussions.microsoft.com> wrote in message
> news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
>
|||Depending on what you finally want to do with it, I am not sure that going
from CSV to XML to rowset is the best way.
At least in the context of SQL Server 2005, you probably want to write a CLR
user defined function that takes the CSV and makes it directly into a
table-valued result.
Otherwise, Adam's suggestions are useful too.
Best regards
Michael
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:uvUdwO7fEHA.536@.TK2MSFTNGP11.phx.gbl...
> Actually I was just looking around MSDN and if you can do it using C# then
> you will have a much easier time with Chris Lovett's XmlCsvReader at
> http://msdn.microsoft.com/library/de...lcsvreader.asp -
> This will give you a much easier time - just put the column names you want
> in the first row of your CSV file/stream and this thing will do the work
> for
> you. The link has a code sample to get you going.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
> news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> may
> http://msdn.microsoft.com/library/de...chtutorial.asp
> to
> (http://msdn.microsoft.com/library/de...-us/cpref/html
> to
> appending
> think
> rights.
>
the ConvertToXML UDF. Here is the xml string that I want
to create which will work with openxml
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
Here is the string that I pass in to the ConvertToXML UDF
'1,Joe,Smith,1/1/01,3'
Here is the Original function
CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
@.Delim varchar (5))
returns varchar (8000)
as
begin
return ('<ROOT><Worktable Value="' + replace (@.InputStr,
@.Delim, '"/><Worktable Value="') + '"/></ROOT>')
end
which yields the following from '1,Joe,Smith,1/1/01,3'
<ROOT><Worktable Value="1"/><Worktable
Value="Joe"/><Worktable Value="Smith"/><Worktable
Value="1/1/01"/><Worktable Value="3"/></ROOT>
which is 5 rows and one column
But I need it to look like this:
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
to get 1 row and 5 columns
so in the function my kludge would look like this:
...
declare @.rownum int
declare @.fName varchar(10)
declare @.lName varchar(10)
declare @.dob datetime
declare @.age int
declare @.pos1 int
declare @.pos2 int
begin
--@.input = '1,Joe,Smith,1/1/01,3'
set @.rownum = Left(@.input, 1)
set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
comma so @.pos1 is at 2nd comma
set @.fName = substring(@.input, 3, @.pos1 - 3)
set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
+ @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
+ @.age + '/><ROOT>
End
If anyone has a suggestion how I could streamline this -
please share because my actual row is more like 50
fields. I am thinking a loop would be more efficient, but
I don't know how to implement a loop in a Sql Server UDF.
Could someone share please?
Thanks,
Ed
set @.pos1 = charindex(@.input, ',', @.pos2) --
Hi Ed,
You might have a slightly easier time converting the input string to XML
using C#/VB (or some other mid-tier programming language). Also from a DB
performance standpoint offloading all this string manipulation elsewhere may
help (although the XML you send over the wire will be bigger) - of course
all this depends on how your app is set up and where your bottlenecks are,
so take this with a grain of salt
You can check out
http://msdn.microsoft.com/library/de...chtutorial.asp
for a good example of implementing a class that supports the foreach loop
construct over an array of string tokens. Combine that with your favorite
TextReader implementation to read through all the "rows" of string tokens to
generate your XML. In terms of generating the XML you can just use string
manipulation or the XmlWriter API
(http://msdn.microsoft.com/library/de...-us/cpref/html
/frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
getting used to but it gives you a higher level abstraction write access to
the underlying XML.
You could loop through your string tokens and create XML that looks like:
<Root value1='{somevalue}' value2='{someothervalue}...</Root> by appending
the iteration number to the name of the attribute value - if there is a
consistent ordering of fields in the file you could then just use the
"colPattern" construct in OPENXML to map the attributes to the appropriate
columns in the table.
If you need to do the whole thing in the server let me know and we can think
about further.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"ED" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
> I am starting to get the hang of this. The trick is in
> the ConvertToXML UDF. Here is the xml string that I want
> to create which will work with openxml
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> Here is the string that I pass in to the ConvertToXML UDF
> '1,Joe,Smith,1/1/01,3'
> Here is the Original function
> ----
> CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
> @.Delim varchar (5))
> returns varchar (8000)
> as
> begin
> return ('<ROOT><Worktable Value="' + replace (@.InputStr,
> @.Delim, '"/><Worktable Value="') + '"/></ROOT>')
> end
> which yields the following from '1,Joe,Smith,1/1/01,3'
> <ROOT><Worktable Value="1"/><Worktable
> Value="Joe"/><Worktable Value="Smith"/><Worktable
> Value="1/1/01"/><Worktable Value="3"/></ROOT>
> which is 5 rows and one column
> ----
> But I need it to look like this:
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> to get 1 row and 5 columns
> so in the function my kludge would look like this:
> ...
> declare @.rownum int
> declare @.fName varchar(10)
> declare @.lName varchar(10)
> declare @.dob datetime
> declare @.age int
> declare @.pos1 int
> declare @.pos2 int
> begin
> --@.input = '1,Joe,Smith,1/1/01,3'
> set @.rownum = Left(@.input, 1)
> set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
> comma so @.pos1 is at 2nd comma
> set @.fName = substring(@.input, 3, @.pos1 - 3)
> set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
> set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
> set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
> set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
> set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
> set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
> set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
> return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
> + @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
> + @.age + '/><ROOT>
> End
> If anyone has a suggestion how I could streamline this -
> please share because my actual row is more like 50
> fields. I am thinking a loop would be more efficient, but
> I don't know how to implement a loop in a Sql Server UDF.
> Could someone share please?
> Thanks,
> Ed
> set @.pos1 = charindex(@.input, ',', @.pos2) --
>
>
|||Actually I was just looking around MSDN and if you can do it using C# then
you will have a much easier time with Chris Lovett's XmlCsvReader at
http://msdn.microsoft.com/library/de...lcsvreader.asp -
This will give you a much easier time - just put the column names you want
in the first row of your CSV file/stream and this thing will do the work for
you. The link has a code sample to get you going.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi Ed,
> You might have a slightly easier time converting the input string to XML
> using C#/VB (or some other mid-tier programming language). Also from a DB
> performance standpoint offloading all this string manipulation elsewhere
may
> help (although the XML you send over the wire will be bigger) - of course
> all this depends on how your app is set up and where your bottlenecks are,
> so take this with a grain of salt
> You can check out
>
http://msdn.microsoft.com/library/de...chtutorial.asp
> for a good example of implementing a class that supports the foreach loop
> construct over an array of string tokens. Combine that with your favorite
> TextReader implementation to read through all the "rows" of string tokens
to
> generate your XML. In terms of generating the XML you can just use string
> manipulation or the XmlWriter API
>
(http://msdn.microsoft.com/library/de...-us/cpref/html
> /frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
> getting used to but it gives you a higher level abstraction write access
to
> the underlying XML.
> You could loop through your string tokens and create XML that looks like:
> <Root value1='{somevalue}' value2='{someothervalue}...</Root> by
appending
> the iteration number to the name of the attribute value - if there is a
> consistent ordering of fields in the file you could then just use the
> "colPattern" construct in OPENXML to map the attributes to the appropriate
> columns in the table.
> If you need to do the whole thing in the server let me know and we can
think
> about further.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ED" <anonymous@.discussions.microsoft.com> wrote in message
> news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
>
|||Depending on what you finally want to do with it, I am not sure that going
from CSV to XML to rowset is the best way.
At least in the context of SQL Server 2005, you probably want to write a CLR
user defined function that takes the CSV and makes it directly into a
table-valued result.
Otherwise, Adam's suggestions are useful too.
Best regards
Michael
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:uvUdwO7fEHA.536@.TK2MSFTNGP11.phx.gbl...
> Actually I was just looking around MSDN and if you can do it using C# then
> you will have a much easier time with Chris Lovett's XmlCsvReader at
> http://msdn.microsoft.com/library/de...lcsvreader.asp -
> This will give you a much easier time - just put the column names you want
> in the first row of your CSV file/stream and this thing will do the work
> for
> you. The link has a code sample to get you going.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
> news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> may
> http://msdn.microsoft.com/library/de...chtutorial.asp
> to
> (http://msdn.microsoft.com/library/de...-us/cpref/html
> to
> appending
> think
> rights.
>
Can not Update in DataGrid - C#
Here is my code :
string connstring = System.Configuration.ConfigurationSettings.AppSettings["myconn"];
string selectquery = "Select * from nhacungcap";
protected System.Web.UI.WebControls.DataGrid DataGrid1;
string insertquery = "Insert into nhacungcap(mancc,tenncc,diachi,dienthoai) values(@.mancc1,@.tenncc1,@.diachi1,@.dienthoai1)";
string updatequery = "Update nhacungcap set mancc=@.mancc, tenncc=@.tenncc, diachi=@.diachi, dienthoai=@.dienthoai where (mancc=@.mancc)";
myconnection.Open();
SqlCommand updatecommand = new SqlCommand(updatequery,myconnection);
// sua truong mancc
updatecommand.Parameters.Add(new SqlParameter("@.mancc",SqlDbType.VarChar,10));
updatecommand.Parameters["@.mancc"].Value = DataGrid1.DataKeys[e.Item.ItemIndex];
// sua truong tenncc
updatecommand.Parameters.Add(new SqlParameter("@.tenncc",SqlDbType.NVarChar,50));
updatecommand.Parameters["@.tenncc"].Value = ((TextBox) e.Item.Cells[3].Controls[0]).Text;
// sua truong diachi
updatecommand.Parameters.Add(new SqlParameter("@.diachi",SqlDbType.NVarChar,200));
updatecommand.Parameters["@.diachi"].Value = ((TextBox) e.Item.Cells[4].Controls[0]).Text;
// sua truong dienthoai
updatecommand.Parameters.Add(new SqlParameter("@.dienthoai",SqlDbType.Char,10));
updatecommand.Parameters["@.dienthoai"].Value = ((TextBox) e.Item.Cells[5].Controls[0]).Text;
// kiem tra lenh thuc thi
int result1 = updatecommand.ExecuteNonQuery();
myconnection.Close();
// dieu kien kiem tra
if (result1 > 0 )
{
lbcheck.Text = "C?p Nh?t Thành công !";
}
// hien thi du lieu
hienthidulieu();
And my error appear, when I edit value in datagrid, I only update all value fields, without value@.mancc .Hu hu hu, I don't know what i must do with it.
Sunday, February 12, 2012
Can not see any server in the available servers list
I successfully installed SQL Server 2005 Express edition in my pc. I ran Visual Basic 6 and used and Ado object to build a connection string to access the server but, I could not see any server in the comobox list.
I always get connection failure messages say that the server does not exitst!!!
Any help please.
Thanks
The installation process does not leave the server open to 'outside' connections. You will have to manually 'open' the server. Here are some useful resources:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html
Friday, February 10, 2012
can not login my database when published to IIS
hi,
i am tring to use the 'xcopy' way to deploy my web site, this is the connection string i am using:
"Data Source=.\SQLEXPRESS;INITIAL CATALOG=;AttachDbFilename='|DataDirectory|\HotStage.mdf';Integrated Security=True;User Instance=True"
it works when in debug mode, but after i depoly it to my XP's IIS, it can not login the database, the error message is :
Cannot Login to the default user database. Login Failed. Login Failed for user xxx\ASPNET
'xxx' is my computer.
i think this problem is cause of improper setting of security, but i really do not know how to set it in this situation, because the database is attached to the db system dynamically.
plz someone help, thx
when you use userinstance in sql server express, once the applicaiton is connected to the database you can not connect to the database using SSMO Express. If you donot want to use userinstance change the connection string . "AttachDbFilename='|DataDirectory|\HotStage.mdf' " this is the part where u are attaching the db in connection string. change it if not needed
refer :
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
Madhu
|||hi,
yes, after i remove the 'User Instanct' clause, and did some permission adjustment, i do connected to my database.
but now i have another strange problem... it says my database is read-only
i have set that folder to hava 'write' permission in IIS.
any idea about this?
can not login my database when published to IIS
hi,
i am tring to use the 'xcopy' way to deploy my web site, this is the connection string i am using:
"Data Source=.\SQLEXPRESS;INITIAL CATALOG=;AttachDbFilename='|DataDirectory|\HotStage.mdf';Integrated Security=True;User Instance=True"
it works when in debug mode, but after i depoly it to my XP's IIS, it can not login the database, the error message is :
Cannot Login to the default user database. Login Failed. Login Failed for user xxx\ASPNET
'xxx' is my computer.
i think this problem is cause of improper setting of security, but i really do not know how to set it in this situation, because the database is attached to the db system dynamically.
plz someone help, thx
when you use userinstance in sql server express, once the applicaiton is connected to the database you can not connect to the database using SSMO Express. If you donot want to use userinstance change the connection string . "AttachDbFilename='|DataDirectory|\HotStage.mdf' " this is the part where u are attaching the db in connection string. change it if not needed
refer :
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
Madhu
|||hi,
yes, after i remove the 'User Instanct' clause, and did some permission adjustment, i do connected to my database.
but now i have another strange problem... it says my database is read-only
i have set that folder to hava 'write' permission in IIS.
any idea about this?
Subscribe to:
Posts (Atom)