CREATE TABLE U(col1 INT);
INSERT INTO U VALUES(2);
INSERT INTO U VALUES(7);
INSERT INTO U VALUES(9);
CREATE TABLE V(col1 INT);
INSERT INTO V VALUES(3);
INSERT INTO V VALUES(7);
INSERT INTO V VALUES(NULL);
SELECT * FROM U WHERE
col1 NOT IN(SELECT col1 FROM V);
i expected it to return 2 and 9 but it returned nothing. can anyone explain?This is because of NULL value in the V table. Comparing to NULL, we don't
know whether the known values are equal or different, so we have to make an
agreement how to deal in such situations. In your case, you see the result.
If you want to get the values you are mentioning, change the query to
SELECT * FROM U WHERE
U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"ichor" <ichor@.hotmail.com> wrote in message
news:OqZWh2FrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> CREATE TABLE U(col1 INT);
> INSERT INTO U VALUES(2);
> INSERT INTO U VALUES(7);
> INSERT INTO U VALUES(9);
> CREATE TABLE V(col1 INT);
> INSERT INTO V VALUES(3);
> INSERT INTO V VALUES(7);
> INSERT INTO V VALUES(NULL);
>
> SELECT * FROM U WHERE
> col1 NOT IN(SELECT col1 FROM V);
> i expected it to return 2 and 9 but it returned nothing. can anyone
> explain?
>|||If you are using the NOT IN Opereator with a subquery and the subquery
contains any NULL values, the subquery will return NULL!. This can be
dangerous, and this is not the case if you use IN.
http://toponewithties.blogspot.com/...es.blogspot.com
"ichor" <ichor@.hotmail.com> wrote in message
news:OqZWh2FrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> CREATE TABLE U(col1 INT);
> INSERT INTO U VALUES(2);
> INSERT INTO U VALUES(7);
> INSERT INTO U VALUES(9);
> CREATE TABLE V(col1 INT);
> INSERT INTO V VALUES(3);
> INSERT INTO V VALUES(7);
> INSERT INTO V VALUES(NULL);
>
> SELECT * FROM U WHERE
> col1 NOT IN(SELECT col1 FROM V);
> i expected it to return 2 and 9 but it returned nothing. can anyone
> explain?
>|||That exact example is given in Itzik Ben-Gan's T-SQL Black Belt column
this month in SQLMag (Don't Avoid the UNKNOWN
<http://www.windowsitpro.com/Article...47010.html?Ad=1> ).
If you want a fuller explanation, you should read his article.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Dejan Sarka wrote:
>This is because of NULL value in the V table. Comparing to NULL, we don't
>know whether the known values are equal or different, so we have to make an
>agreement how to deal in such situations. In your case, you see the result.
>If you want to get the values you are mentioning, change the query to
>SELECT * FROM U WHERE
> U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
>
>|||this is where i took the code from. i have subscribed to sqlmag but cant see
the entire article.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:OA$
QFNGrFHA.3096@.TK2MSFTNGP15.phx.gbl...
That exact example is given in Itzik Ben-Gan's T-SQL Black Belt column this
month in SQLMag (Don't Avoid the UNKNOWN). If you want a fuller explanation
, you should read his article.
mike hodgson
blog: http://sqlnerd.blogspot.com
Dejan Sarka wrote:
This is because of NULL value in the V table. Comparing to NULL, we don't
know whether the known values are equal or different, so we have to make an
agreement how to deal in such situations. In your case, you see the result.
If you want to get the values you are mentioning, change the query to
SELECT * FROM U WHERE
U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Friday, March 30, 2012
Putting Multiple Field data in a report table cell
I'm using report designer and Visual Studio .net 2003. I have a table in the report and would like to put multiple field values in a single cell. Something along the lines of this in the cell: =Fields!Phone.Value, Fields!.Fax.Value.
I don't know how to seperate the values to get them to work in the same table cell. I've tried commas, semi colons, spaces, parens.
How would I do this?=Fields!Phone.Value & " " & Fields!Fax.Value should work.
|||Great that works!
One other question, if I put a symbol (comma, semicolon) in the between the ampersands, how do I get it to leave the cell blank if there is nothing stored in the fields?
For example, say I have =Fields!Phone.Value & ", " & Fields!Fax.Value in the cell but when there is no values for those fields the cell is ", ".|||=Fields!Phone.Value & ISNULL(Fields!Fax.Value,"",",") & Fields!Fax.Value should work?
Otherwise you can try using IIF.
|||I couldn't use IsNull because it says it is not decleared. I can use IIF, so the correct code is =Fields!Phone.Value & IIF(Fields!Fax.Value=nothing,"",",") & Fields!Fax.Value
Thanks for your help.
Wednesday, March 28, 2012
Putting encypted values in a database
hi guys,
Could any one tell me whats the best way to put encypted data into a table.
i have a textfield that a single integer is entered into it. It is then encrypted using DES encrption algorithim.It converts the value to byte(Convert2ByteArray method) I pass the data to a stored procedure which converts it to char before inserting it into the table. when i checked the database all it entered was System.Byte[]. Does anyone know where im goin wrong?
Mairtin
The problem lies because the .ToString method of your byte [] returns "System.Byte[]". You will need to manually convert this over to a string before placing this into your Stored Procedure.System.Converthas a couple methods to help you with this.
byte [] encryptedBytes = GetEncryptedBytes( unencryptedData );
string encryptedString = System.Convert.ToBase64String( encryptedBytes );
To unencrypt the string you must go the other way.
byte encryptedBytes = System.Convert.FromBase64String( encryptedString );
unencryptedData = GetDecryptedString( encryptedBytes );
bill
|||
hey billrob,
i tried that out.
I dont know if it has done what is suppose to! I wrote the encrypted value out to a textfile aswell to see the difference.
in the database the value was Mw==
in the textfile the value looked like ??Xq/ ê à?*?a?]T￥ù?±@.àcl<tTàFE/.(without System.covert used on it )
Somehow i think i may be going wrong,
|||How were you viewing it in the Database? The binary data looks (from textfile) looks like valid data, post some of your code, but remove your Vector and key from the post.ToBase64 and FromBase64 are inverse operations. If you take your binary blob, base64 it, then turn it back into binary, you will have the same thing.
How large did you make your text column in the database that stores this information.
bill
FYI, the Base64String stuff is very similiar to how .NET processes the ViewState.
Putting commas between select statement values
Hello,
This may be a strange request, but I am going to ask about it anyways.
Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this:
NUMBERS
1
2
3
4
How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get:
1,2,3,4
Instead of:
1
2
3
4
Any ideas?
ThanksThe numbers will still be as o column... but try:
SELECT CAST(numbers as varchar)+',' from TEST
This may be a strange request, but I am going to ask about it anyways.
Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this:
NUMBERS
1
2
3
4
How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get:
1,2,3,4
Instead of:
1
2
3
4
Any ideas?
ThanksThe numbers will still be as o column... but try:
SELECT CAST(numbers as varchar)+',' from TEST
Wednesday, March 21, 2012
Pulling values from code behind
In my code behind file I have this function:
1public void getAllSystems()2 {3 MTConnection con = MTConnection.CreateConnection(new Credential(new Context("ClearQuest - Boise","Version 2.0", Micron.Application.Context.Environments.Production),"DSSPROD", Credential.DataSourceTypes.MSSQL, Credential.DataSourceProviders.Odbc));45string sqltest =" select T1.dbid,T12.systemname AS 'ParentSystem',T1.systemname AS 'SupportSystem',T1.systemstate,T1.features,T3.name,T4.stakeholder," +6" T4.fldcolumn_1,T1.systemwebpage,T1.description from ( ( ( ( ( ( ( ( CQ_ADMIN.micronsystem T1 INNER JOIN" +7" CQ_ADMIN.microngroup T6 ON T1.primarysupportgroup = T6.dbid ) INNER JOIN CQ_ADMIN.securitygroups T7 ON T1.secuirtygroup" +8" = T7.dbid ) LEFT OUTER JOIN CQ_ADMIN.parent_child_links T12mm ON T1.dbid = T12mm.child_dbid and 16778862 =" +9" T12mm.child_fielddef_id ) LEFT OUTER JOIN CQ_ADMIN.micronsystem T12 ON T12mm.parent_dbid = T12.dbid )" +10" LEFT OUTER JOIN CQ_ADMIN.parent_child_links T3mm ON T1.dbid = T3mm.parent_dbid and 16804095 =" +11" T3mm.parent_fielddef_id ) LEFT OUTER JOIN CQ_ADMIN.feature_1 T3 ON T3mm.child_dbid = T3.dbid ) LEFT OUTER" +12" JOIN CQ_ADMIN.parent_child_links T4mm ON T3.dbid = T4mm.parent_dbid and 16804263 = T4mm.parent_fielddef_id )" +13" LEFT OUTER JOIN CQ_ADMIN.stakeholder T4 ON T4mm.child_dbid = T4.dbid ) where T1.dbid <> 0 and ((T6.abbreviation =" +14" 'Test ES Eng Software' and T1.systemstate = 'Active')) and ((T7.dbid in (select parent_dbid from" +15" CQ_ADMIN.parent_child_links where parent_fielddef_id = 16778767 and child_dbid in (select child_dbid from" +16" CQ_ADMIN.parent_child_links where parent_fielddef_id = 16777328 and parent_dbid = 33664106)and T12.systemname IS NOT" +17" NULL ))) order by T3.name ASC,T1.systemname ASC";1819 System.Data.DataSet ds = con.ExecuteDataset(CommandType.Text, sqltest);2021foreach (DataRow drCQin ds.Tables[0].Rows)22 {23string parent_system = drCQ["ParentSystem"].ToString();24string child_system = drCQ["SupportSystem"].ToString();25//Response.Write(child_system + "<B><I>'S PARENT IS</I></B> " + parent_system + "<BR />");26 }27 }I would like to pull the values of parent_system and child_system into my main page but I don't know how to do that. Is it even possible?Thanks,
Xana.
If there are mulitple records returned in your result set, then simply bind your returned DataSet to a GridView. I'm assuming you just want to display you data correct?
|||No, I just need to pass the two strings, and all of thier values, to the main page. I am trying to construct a JavaScript array based on the two strings but I need to pass all of the values before I can do that. I already know how to use ASP values and variables in JavaScript but I don't know how to pull the value into the main page for use.
|||How about loading the results into two different HiddenField controls whcih you can then use later from your JavaScript routines.
|||How would I seperate them out into two hiddenfields.
The reason I ask is that with what I am trying to do each child_system has a parent_system that it belongs too. I would lIke to create an associative array out of the two strings. Plus in total the for each loop that they are in returns 90 + results.
Even if I were to create the array in the code behind ( which I don't know how to do (in C#) ) I would still need to pull the array into the main page.
|||You can't. I misunderstood what you were wanting to do. I'd have to think about a solution.
|||Is there any way at all to pull the values into the main page?
|||Can you at all utilize the ClientScriptManager.RegisterArrayDeclartion method?
http://msdn2.microsoft.com/en-us/library/system.web.ui.clientscriptmanager.registerarraydeclaration.aspx
|||How exactly would that script help me? And how would I use it exactly. I am looking at it but not really understanding it.
|||To be honest, I'm not exactly sure. It's just a way to create a JavaScript array from code. The only problem is that the method excepts a string for its ArrayValue field instead of an array of some sort, so creating the parameter itself would be a pain. If I can think of any other ideas, I'll let you know.
|||If I print out the variables into hidden fields with the names being the parent system and the values being the child system would it be possible to, by using a javascript function, call all of the values of the hidden fields?
An example would be there is a drop down menu with the parent systems. Say the user chose TEST_ESENG_SOFTWARE from the menu. TEST_ESENG_SOFTWARE had three child systems, TEST_ONE, TEST_TWO, TEST_THREE ( not using real names ).
With the onchange event for the first drop down menu I could call the function that gets the values from the hidden form fields that have the name TEST_ESENG_SOFTWARE, and then use them to populate a second drop down menu.
Is the above senerio at all possible?? If it is how would I do it?
|||That would be tough to do when the number of items in your data would vary. I'm not a JavaScript person by any means so I don't know how you'd extract values from a series of controls. Is your whole purpose for wanting this in JavaScript for performance? If so, might I suggest using an AJAX cascading drop-down list.
|||The reason for doing this in JavaScript is to avoid using postbacks because clearquest can get quite slow with return large sets of results. I know a method exists that gets the elements it is: getElementsByName() but I do not know exactly how to use it to return the actual values.
Do you know anyone who could help us on this forum? If so please give me a name as I can send them a message. If not, oh well I can try to find something online that can help me more. Your suggestions have been extremely helpful.
|||I wish I knew someone who could help you more, but I don't. It really sounds like you might benefit from using AJAX. Regardless, good luck.
Tuesday, March 20, 2012
Pulling row values from a dataset using embedded code?
I am trying to figure out a way to pull the values of one of my report
columns once the report has been rendered to create a comma separated list of
those values to pass as a paramater to another report. I figure i will have
to do this in the code part of RS. Can anyone help me with the proper
functions and syntax to walk through the rows returned by my report and pull
the values from the column I need.
I imagine it would look like this
function GetItemList()
Dim rtnstr As String
Dim x As Interger
Set rtnstr = ""
for x = 0 to (RowNumber("DatasetName")-1)
rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
next
return rtnstr
end function
What I am stuck on is how to pull the values from the column in the
particular dataset I need and also how to determine the Scope of the dataset
to know how long th loop needs to run for.
ThanksOut of the box, you can only base a report on the following data sources: SQL
Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
into one of these categories; in other words, there is no data processing
extension that allows you to read data from one report into another report.
You should re-design your solution. What about writing a stored procedure
that writes the data to a second table, appending commas as required to
create the csv format you want? You could then report off this second table?
Charles Kangai, MCDBA, MCT
"jordang" wrote:
> I am trying to figure out a way to pull the values of one of my report
> columns once the report has been rendered to create a comma separated list of
> those values to pass as a paramater to another report. I figure i will have
> to do this in the code part of RS. Can anyone help me with the proper
> functions and syntax to walk through the rows returned by my report and pull
> the values from the column I need.
> I imagine it would look like this
> function GetItemList()
> Dim rtnstr As String
> Dim x As Interger
> Set rtnstr = ""
> for x = 0 to (RowNumber("DatasetName")-1)
> rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> next
> return rtnstr
> end function
> What I am stuck on is how to pull the values from the column in the
> particular dataset I need and also how to determine the Scope of the dataset
> to know how long th loop needs to run for.
> Thanks|||Hi Charles
My Report is already driven by a stored procedure, however, there are local
filters the user can apply to the report through the report viewer as well.
So As far as the stored proc generates the dataset, I'm good to handle those
results and create the list i need. The problem is when the user applies a
filter that is specific to that report and thus not sent back to the stored
proc. I was hoping there was a way I could avoid rewriting my stored proc to
include all my report parameters, or furthermore, writing another stored proc
to generate my list.
Thanks for your help.
"Charles Kangai" wrote:
> Out of the box, you can only base a report on the following data sources: SQL
> Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
> into one of these categories; in other words, there is no data processing
> extension that allows you to read data from one report into another report.
> You should re-design your solution. What about writing a stored procedure
> that writes the data to a second table, appending commas as required to
> create the csv format you want? You could then report off this second table?
> Charles Kangai, MCDBA, MCT
>
> "jordang" wrote:
> > I am trying to figure out a way to pull the values of one of my report
> > columns once the report has been rendered to create a comma separated list of
> > those values to pass as a paramater to another report. I figure i will have
> > to do this in the code part of RS. Can anyone help me with the proper
> > functions and syntax to walk through the rows returned by my report and pull
> > the values from the column I need.
> >
> > I imagine it would look like this
> >
> > function GetItemList()
> > Dim rtnstr As String
> > Dim x As Interger
> > Set rtnstr = ""
> > for x = 0 to (RowNumber("DatasetName")-1)
> > rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> > next
> > return rtnstr
> > end function
> >
> > What I am stuck on is how to pull the values from the column in the
> > particular dataset I need and also how to determine the Scope of the dataset
> > to know how long th loop needs to run for.
> >
> > Thanks
columns once the report has been rendered to create a comma separated list of
those values to pass as a paramater to another report. I figure i will have
to do this in the code part of RS. Can anyone help me with the proper
functions and syntax to walk through the rows returned by my report and pull
the values from the column I need.
I imagine it would look like this
function GetItemList()
Dim rtnstr As String
Dim x As Interger
Set rtnstr = ""
for x = 0 to (RowNumber("DatasetName")-1)
rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
next
return rtnstr
end function
What I am stuck on is how to pull the values from the column in the
particular dataset I need and also how to determine the Scope of the dataset
to know how long th loop needs to run for.
ThanksOut of the box, you can only base a report on the following data sources: SQL
Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
into one of these categories; in other words, there is no data processing
extension that allows you to read data from one report into another report.
You should re-design your solution. What about writing a stored procedure
that writes the data to a second table, appending commas as required to
create the csv format you want? You could then report off this second table?
Charles Kangai, MCDBA, MCT
"jordang" wrote:
> I am trying to figure out a way to pull the values of one of my report
> columns once the report has been rendered to create a comma separated list of
> those values to pass as a paramater to another report. I figure i will have
> to do this in the code part of RS. Can anyone help me with the proper
> functions and syntax to walk through the rows returned by my report and pull
> the values from the column I need.
> I imagine it would look like this
> function GetItemList()
> Dim rtnstr As String
> Dim x As Interger
> Set rtnstr = ""
> for x = 0 to (RowNumber("DatasetName")-1)
> rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> next
> return rtnstr
> end function
> What I am stuck on is how to pull the values from the column in the
> particular dataset I need and also how to determine the Scope of the dataset
> to know how long th loop needs to run for.
> Thanks|||Hi Charles
My Report is already driven by a stored procedure, however, there are local
filters the user can apply to the report through the report viewer as well.
So As far as the stored proc generates the dataset, I'm good to handle those
results and create the list i need. The problem is when the user applies a
filter that is specific to that report and thus not sent back to the stored
proc. I was hoping there was a way I could avoid rewriting my stored proc to
include all my report parameters, or furthermore, writing another stored proc
to generate my list.
Thanks for your help.
"Charles Kangai" wrote:
> Out of the box, you can only base a report on the following data sources: SQL
> Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
> into one of these categories; in other words, there is no data processing
> extension that allows you to read data from one report into another report.
> You should re-design your solution. What about writing a stored procedure
> that writes the data to a second table, appending commas as required to
> create the csv format you want? You could then report off this second table?
> Charles Kangai, MCDBA, MCT
>
> "jordang" wrote:
> > I am trying to figure out a way to pull the values of one of my report
> > columns once the report has been rendered to create a comma separated list of
> > those values to pass as a paramater to another report. I figure i will have
> > to do this in the code part of RS. Can anyone help me with the proper
> > functions and syntax to walk through the rows returned by my report and pull
> > the values from the column I need.
> >
> > I imagine it would look like this
> >
> > function GetItemList()
> > Dim rtnstr As String
> > Dim x As Interger
> > Set rtnstr = ""
> > for x = 0 to (RowNumber("DatasetName")-1)
> > rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> > next
> > return rtnstr
> > end function
> >
> > What I am stuck on is how to pull the values from the column in the
> > particular dataset I need and also how to determine the Scope of the dataset
> > to know how long th loop needs to run for.
> >
> > Thanks
pulling ntext values sql server 2005
hi,
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
>
>
>
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||I think i found it, the stored proc doing the actual insert into the
table, was set to @.ESSAYTEXT varchar(200) for that column, we decided
to restrict it to 2000 chars, so changing it to @.ESSAYTEXT
varchar(2000), hopefully that's fix it, the varchar(200) explains the
max(len issue as well.
>
>
>
>
>
>
"phil2phil" <philtwophil@.yahoo.comwrote in message
news:1173717649.377685.103280@.h3g2000cwc.googlegro ups.com...
Yeah.. if you're only inserting 200.. ;-)
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
just to see the max we one had it's always 200.
Thanks.
"phil2phil" <philtwophil@.yahoo.comwrote in message
news:1173714656.510142.22770@.v33g2000cwv.googlegro ups.com...
Quote:
Originally Posted by
hi,
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
>
just to see the max we one had it's always 200.
>
Thanks.
>
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||Hi,
I've tried both Sql sever 2000 QA and Sql server 2005 Management
Studio. For QA i set the Results Max chars to 8192 and in Management
studio i set the max chars per column for Text Retults to 8192 and for
Grid to Non XML 65535, but it's still not returning the full, and each
max length is always 200.
On Mar 12, 12:11 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:
Quote:
Originally Posted by
"phil2phil" <philtwop...@.yahoo.comwrote in message
>
news:1173714656.510142.22770@.v33g2000cwv.googlegro ups.com...
>
Quote:
Originally Posted by
hi,
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
>
Quote:
Originally Posted by
just to see the max we one had it's always 200.
>
Quote:
Originally Posted by
Thanks.
>
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||I think i found it, the stored proc doing the actual insert into the
table, was set to @.ESSAYTEXT varchar(200) for that column, we decided
to restrict it to 2000 chars, so changing it to @.ESSAYTEXT
varchar(2000), hopefully that's fix it, the varchar(200) explains the
max(len issue as well.
On Mar 12, 12:26 pm, "phil2phil" <philtwop...@.yahoo.comwrote:
Quote:
Originally Posted by
Hi,
I've tried both Sql sever 2000 QA and Sql server 2005 Management
Studio. For QA i set the Results Max chars to 8192 and in Management
studio i set the max chars per column for Text Retults to 8192 and for
Grid to Non XML 65535, but it's still not returning the full, and each
max length is always 200.
>
On Mar 12, 12:11 pm, "Greg D. Moore \(Strider\)"
>
<mooregr_deletet...@.greenms.comwrote:
Quote:
Originally Posted by
"phil2phil" <philtwop...@.yahoo.comwrote in message
>
Quote:
Originally Posted by
news:1173714656.510142.22770@.v33g2000cwv.googlegro ups.com...
>
Quote:
Originally Posted by
Quote:
Originally Posted by
hi,
i have a column of type ntext in the db, we're using that to allow
users to store essays they can enter. problem is when i'm running a
select on the ntext column, data seems to be getting cut off, isn't
ntext suppose to hold a lot of data, we wanted to allow them in a min
of 1500 chars, while everytime i do a
select len(max (convert(nvarchar(2000), essaytext)))
>
Quote:
Originally Posted by
Quote:
Originally Posted by
just to see the max we one had it's always 200.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Thanks.
>
Quote:
Originally Posted by
What tool are you using for this? If it's QA, you can adjust what it returns
via a setting.
>
Quote:
Originally Posted by
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
"phil2phil" <philtwophil@.yahoo.comwrote in message
news:1173717649.377685.103280@.h3g2000cwc.googlegro ups.com...
Quote:
Originally Posted by
>I think i found it, the stored proc doing the actual insert into the
table, was set to @.ESSAYTEXT varchar(200) for that column, we decided
to restrict it to 2000 chars, so changing it to @.ESSAYTEXT
varchar(2000), hopefully that's fix it, the varchar(200) explains the
max(len issue as well.
>
Yeah.. if you're only inserting 200.. ;-)
(note that text fields sometimes are better handled with readtext/writetext.
But if you're moving to SQL 2005 any time I'd highly recommend varchar(max)
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Pulling 2 values from 2 textboxes displayed in a third
I have a problem trying to get a value from textbox 1 and textbox 2.
If those two values match the data in the database then it has to
return a third corresponding value to a third textbox (or label) after
a user clicks a submit button. Basically, I am trying to allow users
to enter a value of weight in pounds in textbox 1 and a zip code in
textbox 2 then pulling the shipping amount from a courier's database
and returning that dollar amount to a third textbox or label after
they click submit. How would I write this code in vb.NET?
If those two values match the data in the database then it has to
return a third corresponding value to a third textbox (or label) after
a user clicks a submit button. Basically, I am trying to allow users
to enter a value of weight in pounds in textbox 1 and a zip code in
textbox 2 then pulling the shipping amount from a courier's database
and returning that dollar amount to a third textbox or label after
they click submit. How would I write this code in vb.NET?
Thanks,
TGHere's an example to get you started:
--schema
CREATE TABLE ShippingCharges
(
ShippingWeight int NOT NULL,
PostalCode varchar(5) NOT NULL,
ShippingCharge decimal(9, 2) NOT NULL
CONSTRAINT PK_ShippingCharges
PRIMARY KEY(ShippingWeight, PostalCode)
)
'VB.Net snippet
Try
Dim connection As New SqlConnection("Data
Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI")
connection.Open()
Dim command As New SqlCommand("SELECT @.ShippingCharge =
ShippingCharge FROM ShippingCharges WHERE PostalCode = @.PostalCode AND
ShippingWeight = @.ShippingWeight", connection)
command.Parameters.Add(New SqlParameter("@.ShippingCharge",
SqlDbType.Decimal))
command.Parameters("@.ShippingCharge").Direction =
ParameterDirection.Output
command.Parameters("@.ShippingCharge").Precision = 9
command.Parameters("@.ShippingCharge").Scale = 2
command.Parameters.Add(New SqlParameter("@.ShippingWeight",
Convert.ToInt32(Me.TextBox1.Text)))
command.Parameters.Add(New SqlParameter("@.PostalCode",
Me.TextBox2.Text))
command.ExecuteScalar()
If command.Parameters("@.ShippingCharge").Value Is DBNull.Value
Then
MessageBox.Show("No shipping charge found")
Else
Me.TextBox3.Text =
command.Parameters("@.ShippingCharge").Value.ToString()
End If
connection.Close()
Catch ex As SqlException
MessageBox.Show(ex.ToString)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TG" <tjgraham4@.hotmail.com> wrote in message
news:4822c297.0409231326.4a2a892a@.posting.google.c om...
>I have a problem trying to get a value from textbox 1 and textbox 2.
> If those two values match the data in the database then it has to
> return a third corresponding value to a third textbox (or label) after
> a user clicks a submit button. Basically, I am trying to allow users
> to enter a value of weight in pounds in textbox 1 and a zip code in
> textbox 2 then pulling the shipping amount from a courier's database
> and returning that dollar amount to a third textbox or label after
> they click submit. How would I write this code in vb.NET?
> Thanks,
> TG
Monday, March 12, 2012
Pull only numeric values
I have a varchar field that contains both numeric and text data. I need to pull only numeric, non-null values.
I am fairly new to SQL, so I applogise if this is a really basic question.
Thanks.
hitry:
SELECT col1, col2
FROM table
WHERE (ISNUMERIC(col1) AND col1 IS NOT NULL)
where col1 is your varchar column
Subscribe to:
Posts (Atom)