I am building an application that requires "custom" data elements for
various different clients of the application. For example, consider an
application that comes with a "standard" order form, that perhaphs consists
of 10 fields that we know ahead of time, orderID, Product Name, Product ID,
etc.
Now consider that the application is delivered via an ASP model, and that
clients we sell the software to, wish to customize the order form to thier
personal liking. In this scenario, they may want to only use say 7 of the
standard fields, but they have 7 additional fields that are specific to
themselves. They may have an internal ID field, or pricing fields that are
specific to themselves.
Every client who subscribes needs to be able to customize the order "form"
accordingly. so if our application is used by 100 clients, we would have
potentially 100 versions of the order form, with totally custom questions.
Of course, I don't think we would want to create 100 versions of the table.
that would be a maintenance nightmare, and be very difficult to program
against.
There is another technique which would use a LOT of meta-data to allow the
dynamic capture of the order data via the form, but this doesn't work at all
when it comes time to report on that data.
My question is, does anyone have previous experience in modeling a database
to deal eligently with these types of requirements? I would greatly
appreciate anyones experience with similiar situations and hear what has and
has not worked.
thanks!Hi Mike
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information provided, you have standard fields ( all the customer
are same) and custom fields. To design the database to save all the
information, could you decribe more clear of what the customer fields might
be?
What I mean is, what is the flexibility level of the customer fields: Could
the customer specify the number of the custom fields? Is the customer could
specified a certain range of fileds with fixed datatype or as many fields
and data type as they want?
If they could just have certain custom fields and known datatype, you could
just have one table with standard fields and all the possible fields. If
the customer choose to enter the data in his specific fileds, the data will
be saved in corresponding fields; If not, the columns will be NULL.
Looking forward for you reply.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong, thanks for your reply.
Let me take a step back and give you the bigger picture. I was using the
Orders table as an example of what we do, however, what we currently offer
is the ability for our clients to define ANY "Form", define all the fields,
what types of fields they are, and also what the input type would be
(dropdownlist, checkbox, radiobutton, etc).
Currently, we are using a strict meta-data format for capturing all the
information necessary to create the "Form" which is generated as a web page.
We have a "formtypes" table that defines a formtypeID, and then a related
"questions" table that holds the specific questions and the definition of
those questions associated with a specific FormType. The question could be
defined as numeric, or date, or text, etc. When a "Form" is submitted, it is
writes one record to a "forms" table that is an instance of that form, which
generates a formid, and then we write each response to a "responses" table,
with the corresoponding formid, and questionid. So every single response
from every single form is actually written to this one table. This has
become much too complex, and a HUGE problem is the inability to be able to
report on this data...since it's entirely custom and we don't know what they
are actually capturing.
We are attempting to look at all the formtypes and questions and create a
Taxonomy ...basically pull out "standard" data that all the clients are
asking and put that data into "real" tables. That will be piece of work.
however, we still need to allow clients to add custom fields on top of what
we offer as standard. The goal is if we can map the data they want to
capture into real fields, we will be able to much easier search on that
data, and also easily report on it. The above data model does well for
capturing purely custom data, however I was hoping for a better format that
would allow us to simplify the capture of this data, and also allow
searching and potentially reporting.
We are redoing are data schema, which is why I'm revisiting this question
and seeing if others have had to deal with this level of complexity. One
potential solution I was thinking about was using the new "Yukon" XML field
type to hold an instance of a submitted form. The form could be serialized
into XML, and could have totally different schemas and still be stored in a
single field. Based on what I read, that data can then be indexed, and
actually searched in combination with relations queries. In addition, I read
something about "promotion", which can let you "promote" specific nodes to
be written into a relational field...which could be a solution for allowing
us to have a mechanism to report on key pieces of data.
We are at the early stages of this work, so all options are open to us. Hope
this gives you a better feel for the complexity of what we are trying to
solve, and I look forward to any suggestions or ideas on the "best
practices" for dealing with this specific type of data modeling problem.
Thanks,
Mike
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:FWwU1cj6DHA.2768@.cpmsftngxa07.phx.gbl...
> Hi Mike
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information provided, you have standard fields ( all the customer
> are same) and custom fields. To design the database to save all the
> information, could you decribe more clear of what the customer fields
might
> be?
> What I mean is, what is the flexibility level of the customer fields:
Could
> the customer specify the number of the custom fields? Is the customer
could
> specified a certain range of fileds with fixed datatype or as many fields
> and data type as they want?
> If they could just have certain custom fields and known datatype, you
could
> just have one table with standard fields and all the possible fields. If
> the customer choose to enter the data in his specific fileds, the data
will
> be saved in corresponding fields; If not, the columns will be NULL.
> Looking forward for you reply.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Mike
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As for saving various types of data in one table, based on my knowledge, it
is hard to do it for the data in SQL Server 2000 database needs regular
storage format. Also, to create one table for one customer would not
practical for the maintenance and developing work would be HUGE. XML would
be a choice, you could use XQuery/XPath expressions on the XML Datatype to
extract and search data and also to get data into a computed column that
can serve as a way to promote information from the XML Datatype instance
into the relational context. Actually, you could ask this question in the
newsgroup in the related newsgroup and our corresponding engineers there
would answer your question about it. The newsgroup would be:
http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=micr
osoft.public.sqlserver.xml
or
news:microsoft.public.sqlserver.xml
Thanks for your post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Showing posts with label example. Show all posts
Showing posts with label example. Show all posts
Friday, March 23, 2012
proper db design methodology for "custom data"?
I am building an application that requires "custom" data elements for
various different clients of the application. For example, consider an
application that comes with a "standard" order form, that perhaphs consists
of 10 fields that we know ahead of time, orderID, Product Name, Product ID,
etc.
Now consider that the application is delivered via an ASP model, and that
clients we sell the software to, wish to customize the order form to thier
personal liking. In this scenario, they may want to only use say 7 of the
standard fields, but they have 7 additional fields that are specific to
themselves. They may have an internal ID field, or pricing fields that are
specific to themselves.
Every client who subscribes needs to be able to customize the order "form"
accordingly. so if our application is used by 100 clients, we would have
potentially 100 versions of the order form, with totally custom questions.
Of course, I don't think we would want to create 100 versions of the table.
that would be a maintenance nightmare, and be very difficult to program
against.
There is another technique which would use a LOT of meta-data to allow the
dynamic capture of the order data via the form, but this doesn't work at all
when it comes time to report on that data.
My question is, does anyone have previous experience in modeling a database
to deal eligently with these types of requirements? I would greatly
appreciate anyones experience with similiar situations and hear what has and
has not worked.
thanks!Hi Mike
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information provided, you have standard fields ( all the customer
are same) and custom fields. To design the database to save all the
information, could you decribe more clear of what the customer fields might
be?
What I mean is, what is the flexibility level of the customer fields: Could
the customer specify the number of the custom fields? Is the customer could
specified a certain range of fileds with fixed datatype or as many fields
and data type as they want?
If they could just have certain custom fields and known datatype, you could
just have one table with standard fields and all the possible fields. If
the customer choose to enter the data in his specific fileds, the data will
be saved in corresponding fields; If not, the columns will be NULL.
Looking forward for you reply.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong, thanks for your reply.
Let me take a step back and give you the bigger picture. I was using the
Orders table as an example of what we do, however, what we currently offer
is the ability for our clients to define ANY "Form", define all the fields,
what types of fields they are, and also what the input type would be
(dropdownlist, checkbox, radiobutton, etc).
Currently, we are using a strict meta-data format for capturing all the
information necessary to create the "Form" which is generated as a web page.
We have a "formtypes" table that defines a formtypeID, and then a related
"questions" table that holds the specific questions and the definition of
those questions associated with a specific FormType. The question could be
defined as numeric, or date, or text, etc. When a "Form" is submitted, it is
writes one record to a "forms" table that is an instance of that form, which
generates a formid, and then we write each response to a "responses" table,
with the corresoponding formid, and questionid. So every single response
from every single form is actually written to this one table. This has
become much too complex, and a HUGE problem is the inability to be able to
report on this data...since it's entirely custom and we don't know what they
are actually capturing.
We are attempting to look at all the formtypes and questions and create a
Taxonomy ...basically pull out "standard" data that all the clients are
asking and put that data into "real" tables. That will be piece of work.
however, we still need to allow clients to add custom fields on top of what
we offer as standard. The goal is if we can map the data they want to
capture into real fields, we will be able to much easier search on that
data, and also easily report on it. The above data model does well for
capturing purely custom data, however I was hoping for a better format that
would allow us to simplify the capture of this data, and also allow
searching and potentially reporting.
We are redoing are data schema, which is why I'm revisiting this question
and seeing if others have had to deal with this level of complexity. One
potential solution I was thinking about was using the new "Yukon" XML field
type to hold an instance of a submitted form. The form could be serialized
into XML, and could have totally different schemas and still be stored in a
single field. Based on what I read, that data can then be indexed, and
actually searched in combination with relations queries. In addition, I read
something about "promotion", which can let you "promote" specific nodes to
be written into a relational field...which could be a solution for allowing
us to have a mechanism to report on key pieces of data.
We are at the early stages of this work, so all options are open to us. Hope
this gives you a better feel for the complexity of what we are trying to
solve, and I look forward to any suggestions or ideas on the "best
practices" for dealing with this specific type of data modeling problem.
Thanks,
Mike
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:FWwU1cj6DHA.2768@.cpmsftngxa07.phx.gbl...
might
Could
could
could
will
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As for saving various types of data in one table, based on my knowledge, it
is hard to do it for the data in SQL Server 2000 database needs regular
storage format. Also, to create one table for one customer would not
practical for the maintenance and developing work would be HUGE. XML would
be a choice, you could use XQuery/XPath expressions on the XML Datatype to
extract and search data and also to get data into a computed column that
can serve as a way to promote information from the XML Datatype instance
into the relational context. Actually, you could ask this question in the
newsgroup in the related newsgroup and our corresponding engineers there
would answer your question about it. The newsgroup would be:
http://support.microsoft.com/newsgr...&NewsGroup=micr
osoft.public.sqlserver.xml
or
news:microsoft.public.sqlserver.xml
Thanks for your post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
various different clients of the application. For example, consider an
application that comes with a "standard" order form, that perhaphs consists
of 10 fields that we know ahead of time, orderID, Product Name, Product ID,
etc.
Now consider that the application is delivered via an ASP model, and that
clients we sell the software to, wish to customize the order form to thier
personal liking. In this scenario, they may want to only use say 7 of the
standard fields, but they have 7 additional fields that are specific to
themselves. They may have an internal ID field, or pricing fields that are
specific to themselves.
Every client who subscribes needs to be able to customize the order "form"
accordingly. so if our application is used by 100 clients, we would have
potentially 100 versions of the order form, with totally custom questions.
Of course, I don't think we would want to create 100 versions of the table.
that would be a maintenance nightmare, and be very difficult to program
against.
There is another technique which would use a LOT of meta-data to allow the
dynamic capture of the order data via the form, but this doesn't work at all
when it comes time to report on that data.
My question is, does anyone have previous experience in modeling a database
to deal eligently with these types of requirements? I would greatly
appreciate anyones experience with similiar situations and hear what has and
has not worked.
thanks!Hi Mike
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information provided, you have standard fields ( all the customer
are same) and custom fields. To design the database to save all the
information, could you decribe more clear of what the customer fields might
be?
What I mean is, what is the flexibility level of the customer fields: Could
the customer specify the number of the custom fields? Is the customer could
specified a certain range of fileds with fixed datatype or as many fields
and data type as they want?
If they could just have certain custom fields and known datatype, you could
just have one table with standard fields and all the possible fields. If
the customer choose to enter the data in his specific fileds, the data will
be saved in corresponding fields; If not, the columns will be NULL.
Looking forward for you reply.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong, thanks for your reply.
Let me take a step back and give you the bigger picture. I was using the
Orders table as an example of what we do, however, what we currently offer
is the ability for our clients to define ANY "Form", define all the fields,
what types of fields they are, and also what the input type would be
(dropdownlist, checkbox, radiobutton, etc).
Currently, we are using a strict meta-data format for capturing all the
information necessary to create the "Form" which is generated as a web page.
We have a "formtypes" table that defines a formtypeID, and then a related
"questions" table that holds the specific questions and the definition of
those questions associated with a specific FormType. The question could be
defined as numeric, or date, or text, etc. When a "Form" is submitted, it is
writes one record to a "forms" table that is an instance of that form, which
generates a formid, and then we write each response to a "responses" table,
with the corresoponding formid, and questionid. So every single response
from every single form is actually written to this one table. This has
become much too complex, and a HUGE problem is the inability to be able to
report on this data...since it's entirely custom and we don't know what they
are actually capturing.
We are attempting to look at all the formtypes and questions and create a
Taxonomy ...basically pull out "standard" data that all the clients are
asking and put that data into "real" tables. That will be piece of work.
however, we still need to allow clients to add custom fields on top of what
we offer as standard. The goal is if we can map the data they want to
capture into real fields, we will be able to much easier search on that
data, and also easily report on it. The above data model does well for
capturing purely custom data, however I was hoping for a better format that
would allow us to simplify the capture of this data, and also allow
searching and potentially reporting.
We are redoing are data schema, which is why I'm revisiting this question
and seeing if others have had to deal with this level of complexity. One
potential solution I was thinking about was using the new "Yukon" XML field
type to hold an instance of a submitted form. The form could be serialized
into XML, and could have totally different schemas and still be stored in a
single field. Based on what I read, that data can then be indexed, and
actually searched in combination with relations queries. In addition, I read
something about "promotion", which can let you "promote" specific nodes to
be written into a relational field...which could be a solution for allowing
us to have a mechanism to report on key pieces of data.
We are at the early stages of this work, so all options are open to us. Hope
this gives you a better feel for the complexity of what we are trying to
solve, and I look forward to any suggestions or ideas on the "best
practices" for dealing with this specific type of data modeling problem.
Thanks,
Mike
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:FWwU1cj6DHA.2768@.cpmsftngxa07.phx.gbl...
quote:
> Hi Mike
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information provided, you have standard fields ( all the customer
> are same) and custom fields. To design the database to save all the
> information, could you decribe more clear of what the customer fields
might
quote:
> be?
> What I mean is, what is the flexibility level of the customer fields:
Could
quote:
> the customer specify the number of the custom fields? Is the customer
could
quote:
> specified a certain range of fileds with fixed datatype or as many fields
> and data type as they want?
> If they could just have certain custom fields and known datatype, you
could
quote:
> just have one table with standard fields and all the possible fields. If
> the customer choose to enter the data in his specific fileds, the data
will
quote:|||Hi Mike
> be saved in corresponding fields; If not, the columns will be NULL.
> Looking forward for you reply.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As for saving various types of data in one table, based on my knowledge, it
is hard to do it for the data in SQL Server 2000 database needs regular
storage format. Also, to create one table for one customer would not
practical for the maintenance and developing work would be HUGE. XML would
be a choice, you could use XQuery/XPath expressions on the XML Datatype to
extract and search data and also to get data into a computed column that
can serve as a way to promote information from the XML Datatype instance
into the relational context. Actually, you could ask this question in the
newsgroup in the related newsgroup and our corresponding engineers there
would answer your question about it. The newsgroup would be:
http://support.microsoft.com/newsgr...&NewsGroup=micr
osoft.public.sqlserver.xml
or
news:microsoft.public.sqlserver.xml
Thanks for your post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Wednesday, March 21, 2012
Prompt for a database name
Hi,
Is there a way to prompt someone to enter a database name in a script? For
example, I have a script where I need to update a table to set a column with
a value found in another table in another database. We don't know exactly
what the actual db name is of where to find the table and we would like, in
the script, to be able to allow the user enter the db name so that we can do
the update correctly. We don't want to have the user to manually update
script to specify the db name in the update statement, but rather prompt them
for the name so that we can do the update for them.
Thanks in advance,
DeeSQL Server cannot prompt, it is a service running on a server machine. The client app can. What
app/tool are you using? SQLCMD, OSQL, SSMS, QA, something else?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:130AABB7-8F8B-4F47-8A27-119F022F49F3@.microsoft.com...
> Hi,
> Is there a way to prompt someone to enter a database name in a script? For
> example, I have a script where I need to update a table to set a column with
> a value found in another table in another database. We don't know exactly
> what the actual db name is of where to find the table and we would like, in
> the script, to be able to allow the user enter the db name so that we can do
> the update correctly. We don't want to have the user to manually update
> script to specify the db name in the update statement, but rather prompt them
> for the name so that we can do the update for them.
> Thanks in advance,
> Dee|||Hi Tibor,
We're using Query Analyzer. When we upgrade a customer's database for db
changes, we actually create a script where we have them run it manually via
Query Analyzer.
Thanks!
Dee
"Tibor Karaszi" wrote:
> SQL Server cannot prompt, it is a service running on a server machine. The client app can. What
> app/tool are you using? SQLCMD, OSQL, SSMS, QA, something else?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:130AABB7-8F8B-4F47-8A27-119F022F49F3@.microsoft.com...
> > Hi,
> >
> > Is there a way to prompt someone to enter a database name in a script? For
> > example, I have a script where I need to update a table to set a column with
> > a value found in another table in another database. We don't know exactly
> > what the actual db name is of where to find the table and we would like, in
> > the script, to be able to allow the user enter the db name so that we can do
> > the update correctly. We don't want to have the user to manually update
> > script to specify the db name in the update statement, but rather prompt them
> > for the name so that we can do the update for them.
> >
> > Thanks in advance,
> > Dee
>|||I see. QA has no prompting abilities. In fact, none of the tools that comes with SQL Server has
such. SQLCMD (comes with 2005, you can also download it), has a command-line option for specifying
values for variables (defined inside the script file). Check out -v and $(varname). If you need true
prompting, you have to write some app that pops up the prompt and runs what you have in the script
file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:E6CD55B3-3550-4B9E-B45B-3BE7A81132BD@.microsoft.com...
> Hi Tibor,
> We're using Query Analyzer. When we upgrade a customer's database for db
> changes, we actually create a script where we have them run it manually via
> Query Analyzer.
> Thanks!
> Dee
> "Tibor Karaszi" wrote:
>> SQL Server cannot prompt, it is a service running on a server machine. The client app can. What
>> app/tool are you using? SQLCMD, OSQL, SSMS, QA, something else?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:130AABB7-8F8B-4F47-8A27-119F022F49F3@.microsoft.com...
>> > Hi,
>> >
>> > Is there a way to prompt someone to enter a database name in a script? For
>> > example, I have a script where I need to update a table to set a column with
>> > a value found in another table in another database. We don't know exactly
>> > what the actual db name is of where to find the table and we would like, in
>> > the script, to be able to allow the user enter the db name so that we can do
>> > the update correctly. We don't want to have the user to manually update
>> > script to specify the db name in the update statement, but rather prompt them
>> > for the name so that we can do the update for them.
>> >
>> > Thanks in advance,
>> > Dee
>>
Is there a way to prompt someone to enter a database name in a script? For
example, I have a script where I need to update a table to set a column with
a value found in another table in another database. We don't know exactly
what the actual db name is of where to find the table and we would like, in
the script, to be able to allow the user enter the db name so that we can do
the update correctly. We don't want to have the user to manually update
script to specify the db name in the update statement, but rather prompt them
for the name so that we can do the update for them.
Thanks in advance,
DeeSQL Server cannot prompt, it is a service running on a server machine. The client app can. What
app/tool are you using? SQLCMD, OSQL, SSMS, QA, something else?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:130AABB7-8F8B-4F47-8A27-119F022F49F3@.microsoft.com...
> Hi,
> Is there a way to prompt someone to enter a database name in a script? For
> example, I have a script where I need to update a table to set a column with
> a value found in another table in another database. We don't know exactly
> what the actual db name is of where to find the table and we would like, in
> the script, to be able to allow the user enter the db name so that we can do
> the update correctly. We don't want to have the user to manually update
> script to specify the db name in the update statement, but rather prompt them
> for the name so that we can do the update for them.
> Thanks in advance,
> Dee|||Hi Tibor,
We're using Query Analyzer. When we upgrade a customer's database for db
changes, we actually create a script where we have them run it manually via
Query Analyzer.
Thanks!
Dee
"Tibor Karaszi" wrote:
> SQL Server cannot prompt, it is a service running on a server machine. The client app can. What
> app/tool are you using? SQLCMD, OSQL, SSMS, QA, something else?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:130AABB7-8F8B-4F47-8A27-119F022F49F3@.microsoft.com...
> > Hi,
> >
> > Is there a way to prompt someone to enter a database name in a script? For
> > example, I have a script where I need to update a table to set a column with
> > a value found in another table in another database. We don't know exactly
> > what the actual db name is of where to find the table and we would like, in
> > the script, to be able to allow the user enter the db name so that we can do
> > the update correctly. We don't want to have the user to manually update
> > script to specify the db name in the update statement, but rather prompt them
> > for the name so that we can do the update for them.
> >
> > Thanks in advance,
> > Dee
>|||I see. QA has no prompting abilities. In fact, none of the tools that comes with SQL Server has
such. SQLCMD (comes with 2005, you can also download it), has a command-line option for specifying
values for variables (defined inside the script file). Check out -v and $(varname). If you need true
prompting, you have to write some app that pops up the prompt and runs what you have in the script
file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:E6CD55B3-3550-4B9E-B45B-3BE7A81132BD@.microsoft.com...
> Hi Tibor,
> We're using Query Analyzer. When we upgrade a customer's database for db
> changes, we actually create a script where we have them run it manually via
> Query Analyzer.
> Thanks!
> Dee
> "Tibor Karaszi" wrote:
>> SQL Server cannot prompt, it is a service running on a server machine. The client app can. What
>> app/tool are you using? SQLCMD, OSQL, SSMS, QA, something else?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:130AABB7-8F8B-4F47-8A27-119F022F49F3@.microsoft.com...
>> > Hi,
>> >
>> > Is there a way to prompt someone to enter a database name in a script? For
>> > example, I have a script where I need to update a table to set a column with
>> > a value found in another table in another database. We don't know exactly
>> > what the actual db name is of where to find the table and we would like, in
>> > the script, to be able to allow the user enter the db name so that we can do
>> > the update correctly. We don't want to have the user to manually update
>> > script to specify the db name in the update statement, but rather prompt them
>> > for the name so that we can do the update for them.
>> >
>> > Thanks in advance,
>> > Dee
>>
Promp for info when executing stored procedure
Hello,
How can i do to ask for input parameters when one stored procedure is
executed?
For example:
I have one sp test that has one input parameter(exec sp_test param1), if i
execute the stored procedure without the input parameter its returned the
following error:
Server: Msg 201, Level 16, State 4, Procedure dba_sp_defrag_obj, Line 0
Procedure 'sp_test' expects parameter '@.param1', which was not supplied.
All i want to do is to advertise before the execution the need of input
parameter:
ex:
Insert value for param1:
Insert value for x:
Insert value for y:
Insert value for z:
and so on.
Thanks and best regards,
Jorge MendesTry this to get the list of parameters:
Select * from INFORMATION_SCHEMA.Parameters
Where specific_name = '<YourprocedureName>'
order by Ordinal_position
HTH, Jens Suessmeyer.
*** Sent via Developersdex http://www.developersdex.com ***
How can i do to ask for input parameters when one stored procedure is
executed?
For example:
I have one sp test that has one input parameter(exec sp_test param1), if i
execute the stored procedure without the input parameter its returned the
following error:
Server: Msg 201, Level 16, State 4, Procedure dba_sp_defrag_obj, Line 0
Procedure 'sp_test' expects parameter '@.param1', which was not supplied.
All i want to do is to advertise before the execution the need of input
parameter:
ex:
Insert value for param1:
Insert value for x:
Insert value for y:
Insert value for z:
and so on.
Thanks and best regards,
Jorge MendesTry this to get the list of parameters:
Select * from INFORMATION_SCHEMA.Parameters
Where specific_name = '<YourprocedureName>'
order by Ordinal_position
HTH, Jens Suessmeyer.
*** Sent via Developersdex http://www.developersdex.com ***
Monday, March 12, 2012
programmaticaly connecting and accessing a database
I have not been able to find a code example anywhere. Someone please post the code to do this. I would be forever grateful.
I would like the code to be in C#. I want to connect to a database and then select all from a table.
thanks
Forever is a very long time my new friend.
(you need to list all the fields yourself. in my example, I just have one field. You can also leave out the WHERE clause and you will get all records).
try
{
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CodeCampSV06"].ConnectionString);
sqlConnection.Open();
string sqlSelect = "select VistaSlotsId FROM attendees WHERE Username = @.Username";
SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection);
sqlCommand.Parameters.Add("@.Username", SqlDbType.VarChar).Value = username;
vistaIdStatus = (int)sqlCommand.ExecuteScalar();
sqlConnection.Close();
sqlConnection.Dispose();
}
catch (Exception ee)
{
throw new ApplicationException(ee.ToString());
}
ok thanks for all the help. However i am having trouble trying to figure out what "CodeCampSV06" is. Is it the name of the DB? if so, it is not working for me.
thanks
|||Its the name of the connectionString in the application's web.config file.
bullpit
|||Have a look at this:
http://msdn2.microsoft.com/en-us/library/system.configuration.configurationmanager.connectionstrings.aspx
good luck...
bullpit
|||I have now used the correct string for webconfig.
But Now i keep getting this error
Error 2 The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?) C:\mine\MyProjects\Work4Tips\insert.aspx 19 9 C:\...\Work4Tips\
c
how do I solve this? The page i am trying to get this to run in is using masterpages. I am placing the code between the server tags and in these tags between a clickon method. Do I have to use a directive, if so what is it?
thanks for all your help... I am just so lost right now.
|||Add this to your using directives list at the top of your aspx.cs file:
using
System.Data.SqlClient;using
System.Data;bullpit
|||what if I am using inline code and not code behind?
does not work anywhere I place it.
|||You can use this with your page directives at the top of the aspx page.
<%@. Import Namespace="System.Data" %>
bullpit
|||You can also use a fully qualified statement like this:
System.Data.SqlClient.SqlConnection =new System.Data.SqlClient.SqlConnection(...)
bullpit|||
Correction to last post:
System.Data.SqlClient.SqlConnection sqlConnection =new System.Data.SqlClient.SqlConnection
Programmatically Render Report to PDF
Hello,
I'm looking for an example on how to programmatically render a report to pdf in C#. I don't want to use a url with the rs:Format=pdf querystring. Any help would be much appreciated!!
Hello,
Have a look at this doc page:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
There is a full example of how to render reports programmatically using the Render method.
-Chris
|||Hi Chris,
Thanks for your response. I took a look at the link you gave me and now I'm getting the following error:
There was an error opening this document. The file is damaged and cannot be repaired.
Here is the code I'm using:
localhost.ReportingService rs = new localhost.ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream;
string[] StreamIdentifiers;
string OptionalParam = null;
string deviceInfo = "<DeviceInfo>" +
" <OutputFormat>PDF</OutputFormat>" +
" <PageWidth>8.5in</PageWidth>" +
" <PageHeight>11in</PageHeight>" +
" <MarginTop>0.5in</MarginTop>" +
" <MarginLeft>0.5in</MarginLeft>" +
" <MarginRight>0.5in</MarginRight>" +
" <MarginBottom>0.5in</MarginBottom>" +
"</DeviceInfo>";
string mimetype;
string encoding;
string historyid = null;
localhost.ParameterValue[] optionalParams = new localhost.ParameterValue[1];
optionalParams[0] = new localhost.ParameterValue();
optionalParams[0].Name = "applicantid";
optionalParams[0].Value = "10";
localhost.Warning[] optionalWarnings = null;
ResultStream = rs.Render("/employmentapplication/printableemploymentapplication",
"pdf",
historyid,
deviceInfo,
optionalParams,
null,
null,
out encoding,
out mimetype,
out optionalParams,
out optionalWarnings,
out StreamIdentifiers);
Response.BinaryWrite(ResultStream);
No matter which report I try to open, I get the same error. Any help is greatly appreciated!!!
Thanks!!
Daniel
|||You get that error when you attempt to open the document?
It could be that there is some extraneous stuff being written to the HTTP stream to which you do your BinaryWrite. I suggest writing "ResultStream" to a file to isolate where corruption, if any, may be occurring.
Also, the OutputFormat value in DeviceInfo is not necessary when rendering to PDF since you're specifying the render format in the Render method call.
-Chris
|||There shouldn't be any extra bytes in the response stream so you shouldn't worry about that. Make sure that you are actually closing the BinaryWriter. Many of the CLR streams buffer internally, so if they are not closed completely then there can be problems where all of the data is not being written.|||
Hi John -
Thanks for you response. How do I close Response.BinaryWriter? Do you have an example of programmatically rendering a report to pdf that I can look at? Thanks again for all your help!!!
|||From the RS side of the things, you are doing things correctly. As Chris pointed out, I would look into making sure that you are properly handling the Response stream. If you are writing any other data to it, then you will run into problems.
You should be able to call .Flush() on the stream to make sure the contents are completely written out.
-John
|||I was running into the same issue today... calling Flush() and Close() on the response object fixed my issues.
--Andy
|||Hello,
I have successfully implemented the sample referenced above with a VB.NET console app. I would like to build a CLR stored procedure that implements the same code, programmatically generating a PDF report and outputting it to a file. I'm running into permissions issues.
Anybody had any luck calling the Reporting Services Web Service API from inside a CLR stored procedure?
Ken
|||Renskemo, you might want to take this question over to the following forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1
Programmatically Render Report to PDF
Hello,
I'm looking for an example on how to programmatically render a report to pdf in C#. I don't want to use a url with the rs:Format=pdf querystring. Any help would be much appreciated!!
Hello,
Have a look at this doc page:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
There is a full example of how to render reports programmatically using the Render method.
-Chris
|||Hi Chris,
Thanks for your response. I took a look at the link you gave me and now I'm getting the following error:
There was an error opening this document. The file is damaged and cannot be repaired.
Here is the code I'm using:
localhost.ReportingService rs = new localhost.ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream;
string[] StreamIdentifiers;
string OptionalParam = null;
string deviceInfo = "<DeviceInfo>" +
" <OutputFormat>PDF</OutputFormat>" +
" <PageWidth>8.5in</PageWidth>" +
" <PageHeight>11in</PageHeight>" +
" <MarginTop>0.5in</MarginTop>" +
" <MarginLeft>0.5in</MarginLeft>" +
" <MarginRight>0.5in</MarginRight>" +
" <MarginBottom>0.5in</MarginBottom>" +
"</DeviceInfo>";
string mimetype;
string encoding;
string historyid = null;
localhost.ParameterValue[] optionalParams = new localhost.ParameterValue[1];
optionalParams[0] = new localhost.ParameterValue();
optionalParams[0].Name = "applicantid";
optionalParams[0].Value = "10";
localhost.Warning[] optionalWarnings = null;
ResultStream = rs.Render("/employmentapplication/printableemploymentapplication",
"pdf",
historyid,
deviceInfo,
optionalParams,
null,
null,
out encoding,
out mimetype,
out optionalParams,
out optionalWarnings,
out StreamIdentifiers);
Response.BinaryWrite(ResultStream);
No matter which report I try to open, I get the same error. Any help is greatly appreciated!!!
Thanks!!
Daniel
|||You get that error when you attempt to open the document?
It could be that there is some extraneous stuff being written to the HTTP stream to which you do your BinaryWrite. I suggest writing "ResultStream" to a file to isolate where corruption, if any, may be occurring.
Also, the OutputFormat value in DeviceInfo is not necessary when rendering to PDF since you're specifying the render format in the Render method call.
-Chris
|||There shouldn't be any extra bytes in the response stream so you shouldn't worry about that. Make sure that you are actually closing the BinaryWriter. Many of the CLR streams buffer internally, so if they are not closed completely then there can be problems where all of the data is not being written.|||
Hi John -
Thanks for you response. How do I close Response.BinaryWriter? Do you have an example of programmatically rendering a report to pdf that I can look at? Thanks again for all your help!!!
|||From the RS side of the things, you are doing things correctly. As Chris pointed out, I would look into making sure that you are properly handling the Response stream. If you are writing any other data to it, then you will run into problems.
You should be able to call .Flush() on the stream to make sure the contents are completely written out.
-John
|||I was running into the same issue today... calling Flush() and Close() on the response object fixed my issues.
--Andy
|||Hello,
I have successfully implemented the sample referenced above with a VB.NET console app. I would like to build a CLR stored procedure that implements the same code, programmatically generating a PDF report and outputting it to a file. I'm running into permissions issues.
Anybody had any luck calling the Reporting Services Web Service API from inside a CLR stored procedure?
Ken
|||Renskemo, you might want to take this question over to the following forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1
Wednesday, March 7, 2012
Programmatically create a Data Conversion Transformation
Hello,
does anyone know of a good example of how to programmatically add a Data Conversion Transformation to a package? I have come so far that I have the component in my dataflow task, but I don't know how to set the properties of it. That is, how to determine the columns that should be converted and to what data type etc.
I (as far as I understand) need a data conversion transformation since I have not managed to create (via the designer) a package that reads data from an AS400 via DB2OLEDB and stores it in a SQL Server 2005 Database. I keep getting the error "string cannot be converted from unicode to non-unicode" (or something like that) and by searching this forum I learned that the data conversion component might do the trick.
I added this conversion to a manually designed package and it solved the error, but I don't know how to re-create this package programmatically. I would really appreciate some help on this.
Also, if someone has managed to import data from AS400 to SQL server 2005 via OLEDB and NOT got the string conversion error, please let me know!
Regards,
Annika
This sample should give you an idea:
dataConvert = pipeline.ComponentMetaDataCollection.New();
dataConvert.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";
IDTSDesigntimeComponent90 compDataConvert = dataConvert.Instantiate();
compDataConvert.ProvideComponentProperties();
// Add path between the upstream component and the data convert.
int pathID;
AddPath(source, dataConvert, out pathID);
IDTSInput90 input = dataConvert.InputCollection[0];
IDTSOutput90 output = dataConvert.OutputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Find the column to be converted in the upstream collection.
// Assuming to use the first one.
int columnToConvertLineageID = vInput.VirtualInputColumnCollection[0].LineageID;
IDTSInputColumn90 inputColumn = compDataConvert.SetUsageType(input.ID, vInput, columnToConvertLineageID, DTSUsageType.UT_READONLY);
// Insert the output column. Assuming to insert at the index 0.
IDTSOutputColumn90 dcOutputColumn = compDataConvert.InsertOutputColumnAt(output.ID, 0, "Out col. name", "Col. description");
if (inputColumn != null && dcOutputColumn != null)
{
// Set data type properties of the output column -- to convert to
compDataConvert.SetOutputColumnDataTypeProperties(output.ID, dcOutputColumn.ID, ...);
// Map input and output column.
compDataConvert.SetOutputColumnProperty(output.ID, dcOutputColumn.ID, "SourceInputColumnLineageID", outputColumn.LineageID);
}
|||Hi,
I just wanted to let you know that I tried your suggestion and it works fine! Thanks!
//Annika
Programmatically create a Data Conversion Transformation
Hello,
does anyone know of a good example of how to programmatically add a Data Conversion Transformation to a package? I have come so far that I have the component in my dataflow task, but I don't know how to set the properties of it. That is, how to determine the columns that should be converted and to what data type etc.
I (as far as I understand) need a data conversion transformation since I have not managed to create (via the designer) a package that reads data from an AS400 via DB2OLEDB and stores it in a SQL Server 2005 Database. I keep getting the error "string cannot be converted from unicode to non-unicode" (or something like that) and by searching this forum I learned that the data conversion component might do the trick.
I added this conversion to a manually designed package and it solved the error, but I don't know how to re-create this package programmatically. I would really appreciate some help on this.
Also, if someone has managed to import data from AS400 to SQL server 2005 via OLEDB and NOT got the string conversion error, please let me know!
Regards,
Annika
This sample should give you an idea:
dataConvert = pipeline.ComponentMetaDataCollection.New();
dataConvert.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";
IDTSDesigntimeComponent90 compDataConvert = dataConvert.Instantiate();
compDataConvert.ProvideComponentProperties();
// Add path between the upstream component and the data convert.
int pathID;
AddPath(source, dataConvert, out pathID);
IDTSInput90 input = dataConvert.InputCollection[0];
IDTSOutput90 output = dataConvert.OutputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Find the column to be converted in the upstream collection.
// Assuming to use the first one.
int columnToConvertLineageID = vInput.VirtualInputColumnCollection[0].LineageID;
IDTSInputColumn90 inputColumn = compDataConvert.SetUsageType(input.ID, vInput, columnToConvertLineageID, DTSUsageType.UT_READONLY);
// Insert the output column. Assuming to insert at the index 0.
IDTSOutputColumn90 dcOutputColumn = compDataConvert.InsertOutputColumnAt(output.ID, 0, "Out col. name", "Col. description");
if (inputColumn != null && dcOutputColumn != null)
{
// Set data type properties of the output column -- to convert to
compDataConvert.SetOutputColumnDataTypeProperties(output.ID, dcOutputColumn.ID, ...);
// Map input and output column.
compDataConvert.SetOutputColumnProperty(output.ID, dcOutputColumn.ID, "SourceInputColumnLineageID", outputColumn.LineageID);
}
|||Hi,
I just wanted to let you know that I tried your suggestion and it works fine! Thanks!
//Annika
Programmatically altering object code
Has anyone developed a way to programmatically alter the code for multiple
stored procs, functions, etc.?
For example: suppose your database contains hundreds of stored procedures
which access a certain table. Along comes a requirement to rename this
table. It's a lot of tedious work to have to individually open each proc in
QA/EM and change the code. I would like to be able to simply type something
like:
EXEC sp_alter_proc_code 'OldTable', 'NewTable'
And presto, all procs that contain SQL statements that query OldTable are
now pointing to NewTable!
Note that you cannot run an UPDATE statement against syscomment's text field
(e.g., UPDATE syscomments SET text = REPLACE(text, 'OldTable', 'NewTable')),
since this is a computed field.
One solution that I had experimented with was to capture the contents of
syscomment's text column into a variable, run REPLACE against that variable,
and EXEC it. But the problem with that is, a variable can hold only 8000
characters.
I would be interested to hear if anyone has developed a workable solution
for this.>It's a lot of tedious work to have to individually open each proc in
>QA/EM and change the code.
Script all the procs out into a single file as CREATEs (without the
delete!) Open the script in Query Analyzer. Change all the CREATE
PROCs to ALTER PROC. Change all occurances of the old table name to
the new table name - there is a REPLACE ALL feature.
Execute the script.
Roy Harvey
Beacon Falls, CT|||CadeBryant wrote:
> Has anyone developed a way to programmatically alter the code for
> multiple stored procs, functions, etc.?
> For example: suppose your database contains hundreds of stored
> procedures which access a certain table. Along comes a requirement
> to rename this table. It's a lot of tedious work to have to
> individually open each proc in QA/EM and change the code. I would
> like to be able to simply type something like:
> EXEC sp_alter_proc_code 'OldTable', 'NewTable'
> And presto, all procs that contain SQL statements that query OldTable
> are now pointing to NewTable!
> Note that you cannot run an UPDATE statement against syscomment's
> text field (e.g., UPDATE syscomments SET text = REPLACE(text,
> 'OldTable', 'NewTable')), since this is a computed field.
> One solution that I had experimented with was to capture the contents
> of syscomment's text column into a variable, run REPLACE against that
> variable, and EXEC it. But the problem with that is, a variable can
> hold only 8000 characters.
> I would be interested to hear if anyone has developed a workable
> solution for this.
If you are managing your stored procedures using version control
software outside of SQL Server, then you can simply perform a find and
replace. You really should not be managing your procedures using SQL
Server. If you are, I would recommend you extract all procedures and get
them in some form of version control software before beginning this
process.
David Gugick - SQL Server MVP
Quest Software
stored procs, functions, etc.?
For example: suppose your database contains hundreds of stored procedures
which access a certain table. Along comes a requirement to rename this
table. It's a lot of tedious work to have to individually open each proc in
QA/EM and change the code. I would like to be able to simply type something
like:
EXEC sp_alter_proc_code 'OldTable', 'NewTable'
And presto, all procs that contain SQL statements that query OldTable are
now pointing to NewTable!
Note that you cannot run an UPDATE statement against syscomment's text field
(e.g., UPDATE syscomments SET text = REPLACE(text, 'OldTable', 'NewTable')),
since this is a computed field.
One solution that I had experimented with was to capture the contents of
syscomment's text column into a variable, run REPLACE against that variable,
and EXEC it. But the problem with that is, a variable can hold only 8000
characters.
I would be interested to hear if anyone has developed a workable solution
for this.>It's a lot of tedious work to have to individually open each proc in
>QA/EM and change the code.
Script all the procs out into a single file as CREATEs (without the
delete!) Open the script in Query Analyzer. Change all the CREATE
PROCs to ALTER PROC. Change all occurances of the old table name to
the new table name - there is a REPLACE ALL feature.
Execute the script.
Roy Harvey
Beacon Falls, CT|||CadeBryant wrote:
> Has anyone developed a way to programmatically alter the code for
> multiple stored procs, functions, etc.?
> For example: suppose your database contains hundreds of stored
> procedures which access a certain table. Along comes a requirement
> to rename this table. It's a lot of tedious work to have to
> individually open each proc in QA/EM and change the code. I would
> like to be able to simply type something like:
> EXEC sp_alter_proc_code 'OldTable', 'NewTable'
> And presto, all procs that contain SQL statements that query OldTable
> are now pointing to NewTable!
> Note that you cannot run an UPDATE statement against syscomment's
> text field (e.g., UPDATE syscomments SET text = REPLACE(text,
> 'OldTable', 'NewTable')), since this is a computed field.
> One solution that I had experimented with was to capture the contents
> of syscomment's text column into a variable, run REPLACE against that
> variable, and EXEC it. But the problem with that is, a variable can
> hold only 8000 characters.
> I would be interested to hear if anyone has developed a workable
> solution for this.
If you are managing your stored procedures using version control
software outside of SQL Server, then you can simply perform a find and
replace. You really should not be managing your procedures using SQL
Server. If you are, I would recommend you extract all procedures and get
them in some form of version control software before beginning this
process.
David Gugick - SQL Server MVP
Quest Software
Programmatically Accessing an SQLDataSource with a "SELECT COUNT(*)" query.
I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be
Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView)
Dim row As DataRow = datastuff.Table.Rows(0)
Dim installtype As Integer = row("InstallMethod")
Dim install As String = row("Install").ToString
Dim notes As String = row("Notes").ToString
The above only works on a single row, of course. If I needed more, I know I can loop it.
The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"
My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query.
Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView)
Dim row As DataRow = datastuff.Table.Rows(0)
Dim count As Integer = row("rowcnt")
The query here is "SELECT COUNT(*) as rowcnt FROM Software"
The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value.
Where am I going wrong here?
The following should work.
Dim datastuffAs System.Data.DataView =CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), System.Data.DataView)Dim drAs System.Data.DataRow = datastuff .Table.Rows(0)
Dim mycountAsString = Convert.ToInt32(dr("rowcnt")).ToString()'Label1.Text = mycount
|||Hi there,
Aren't you getting the row count from your SELECT COUNT query (1 row obviously)? Instead of getting the result value from that query?
gonzzas
|||
It's very similar to what I've tested out, but that exact code will show that mycount = "1" instead of the actual value.
What is interesting is the GridView control I set up on the test page is outputting the correct result.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="srcSoftwareUsage" Width="527px">
<Columns>
<asp:BoundField DataField="rowcnt" HeaderText="rowcnt" ReadOnly="True" SortExpression="rowcnt" />
</Columns>
</asp:GridView>
Now, it's obviously accessing the column named rowcnt. I can debug my code and manually look at the column values in DataView.Table.Rows(0) and it shows the value 1 and nothing more.
Yes, I can use either code to get the correct count from my query. What is your SqlDataSource code?
Here is what I tested:
Dim dvAs System.Data.DataView =CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), System.Data.DataView)Dim rowAs System.Data.DataRow = dv.Table.Rows(0)
' For Each row As System.Data.DataRow In dv.Table.Rows
Dim mycountAsString = Convert.ToInt32(row("rowcnt")).ToString()Label2.Text = mycount
' Next
|||Murphy's Law probably applies as I didn't give you thecomplete story - I naively thought this part shouldn't matter as it'sthe table output is identical.
It appears to be my sql query.
I'm not actually looking for the count of rows in the Software table, but I'm looking for the count of times a particular row in Software is referenced by 2 other tables - RoleSoft and TeamSoft
With my simple query above, both the code and GridView worked. With this one - the GridView works, the code doesn't.
SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = @.Id) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = @.Id)) AS derivedtbl_1
I thought it was a moot point as the table output appears identical from each query. Obviously I'm wrong. I'm imagining the derivedtbl_1 is probably where I'm getting bogus data in the code.
1> SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = 2) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = 2))
AS derivedtbl_1
2> go
rowcnt
----
3
(1 rows affected)
1> SELECT COUNT(*) as rowcnt FROM Software
2> go
rowcnt
----
8
(1 rows affected)
Bah. I figured it out. It wasn't even the SQL statement. I had updated the @.Id parameter in the srcSoftwareUsage_Selecting event handler and I misused a global variable. It kept setting @.Id to 1 and the count for that Id was always 1.
Now I feel stupid for wasting your time and mine on this. Thanks for the help, though.
Monday, February 20, 2012
Programatically create a script
I need some help with the following issue. I need vb.net code that will create a T-SQL script. For example given the orders table in northwind I would get the followingwith indexes, RI, ect. :
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL ,
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]
GO
CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO
CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO
CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO
CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO
CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
GO
CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
GO
CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO
CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [dbo].[Shippers] (
[ShipperID]
)
GO
It's not VB.NET code, it's a component, but here's something that does what you want:
http://thycotic.com/dotnet_dbscripter.html
program flow question
I am trying to figure out how to make a report stop at a certain value. For example say I have five categories that I can assign an item. I would like to be able to have the report pull just the first category that applies to the item. For instance I have a bike that falls under categories 1 and 3 and a car the falls under 2, 3, and 5. How can I make it so the report will only show the details that apply to the first category the item falls under. Thanks.
So do you have a category field coming in the dataset agianst your items, and user is gonna select one category or may be more than one.
If i got it correct this can be done by using filter the dataset for that parameter may be using where clause or may be using dataset filter or even at table filter.
|||Actually I was able to solve this problem. I changed the view I was pulling from so the categories would be assigned numbers. Then I declare a varable and set it to Min(category) with the same from and where clauses. Then I just add to the where clause to have the catergory equal the varable set to min.
Subscribe to:
Posts (Atom)