transfer Data From Acces: populate

Showing posts with label populate. Show all posts

Friday, March 30, 2012

how do I assign a string to a parameter Im passing to a select statement?

Hello,

I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product.

I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here:

http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx

I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.

I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@.StatusList,','))) OR @.StatusList IS NULL)

but now I don't know how to assign the string value to the parameter, say to '@.solution_id'.

my current select statement which was generated by Visual Studio 2005 looks like this:

SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @.solution_ID)

...but this only pulls results for the first item checked in the checkboxlist.

Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.

First make sure you have createddbo.fn_Split .

SELECT [test], [owner], [date]FROM [test_table]WHERE ([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)

I am not sure "OR @.solution_IDISNULL" should be added,you have to decide it according to your logic.

You are required to pass @.solution_ID to the statement(1,3,4,6 etc) then you can get corresponding test.

Hope this helps.

|||

Thanks for your response. If I'm following you, I do understand that I need to pass @.solution_ID to the select statement like you showed. I have a string of values that I created from iterating through CheckBoxList to find selected boxes. My question is, how do I assign the value of this string to @.solution_ID?

Regards,

Daniel

|||

Assume you have checkboxlist Check1, using following code to get @.solution_ID :

for (int i = 0; i < Check1.Items.Count; i++)
{
if (Check1.Items[i].Selected)
{
// List the selected items
solution_ID = solution_ID + Check1.Items[i].Text;
solution_ID = solution_ID +",";
}
}

Then connect with DB:

SqlCommand sqlcmd = new SqlCommand("SELECT [test], [owner], [date]FROM [test_table]WHERE
([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)", sqlconn);
sqlcmd.Parameters.AddWithValue("@.solution_ID",solution_ID);
sqlconn.Open();
SqlDataReader sdr = sqlcmd.ExecuteReader();

.............

You 'd bette put bold sql script into a stored procedure.

hope this helps.

Monday, March 12, 2012

How can we INFORMATION_SCHEMA on a different server?

We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECT LTRIM(RTRIM(routine_name)) as Save_Query
FROM [B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.This is a multi-part message in MIME format.
--010400070406000708090003
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
What OLE DB provider is "Darmstadtium"? What kind of server is server
B? Microsoft SQL Server? If so, what version of SQL Server? Can you
query any object on the remote server? (Every login to a SQL server
should be able to read master.dbo.sysobjects - can you successfully
execute "SELECT * FROM B.master.dbo.sysobjects"?)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hamish Laws wrote:
>We've working on a system that is being used to populate various tables in
>databases on multiple servers.
>If the application is on Server 'A' and is trying to find information about
>stored procedures on Server 'B' we're getting problems with the following
>query
>SELECT LTRIM(RTRIM(routine_name)) as Save_Query
>FROM [B].[dbName].INFORMATION_SCHEMA.routines
>ORDER BY routine_name
>
>the error message is
>OLE DB provider 'Darmstadtium' does not contain table
>'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
>or the current user does not have permissions on that table.
>Does anybody have any ideas for getting this working?
>There is a logon for Server B with the same logon that Server A is using and
>they have System Administrator as server roles.
>
--010400070406000708090003
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>What OLE DB provider is "Darmstadtium</tt><tt>"?Â What kind of
server is server B?Â Microsoft SQL Server?Â If so, what version of SQL
Server?Â Can you query any object on the remote server?Â (Every login
to a SQL server should be able to read master.dbo.sysobjects - can you
successfully execute "SELECT * FROM B.master.dbo.sysobjects"?)</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Hamish Laws wrote:
<blockquote cite="midA2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com"
type="cite">
<pre wrap="">We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECT LTRIM(RTRIM(routine_name)) as Save_Query
FROM [B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.
</pre>
</blockquote>
</body>
</html>
--010400070406000708090003--|||I think you will have a problem with the INFORMATION_SCHEMA views. The views actually only exists in
the master database (in 2000, in 7.0 and 2005 they are in each database), hence your problem. Try
the system tables instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> We've working on a system that is being used to populate various tables in
> databases on multiple servers.
> If the application is on Server 'A' and is trying to find information about
> stored procedures on Server 'B' we're getting problems with the following
> query
> SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> FROM [B].[dbName].INFORMATION_SCHEMA.routines
> ORDER BY routine_name
>
> the error message is
> OLE DB provider 'Darmstadtium' does not contain table
> '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> or the current user does not have permissions on that table.
> Does anybody have any ideas for getting this working?
> There is a logon for Server B with the same logon that Server A is using and
> they have System Administrator as server roles.|||"Mike Hodgson" wrote:
> What OLE DB provider is "Darmstadtium"?
Sorry, bad editing on my part.
Darmstadtium is the actual name of the server I called 'B'
> What kind of server is server
> B? Microsoft SQL Server? If so, what version of SQL Server?
2000
> Can you
> query any object on the remote server? (Every login to a SQL server
> should be able to read master.dbo.sysobjects - can you successfully
> execute "SELECT * FROM B.master.dbo.sysobjects"?)
>
Yep, I can query other objects on the server.
If I connect to the second server using query analyzer and run the query
direct it works fine so it looks to me like the view isn't available as part
of the connection on a remote server
I took Tibor Karaszi's advice and rewrote it to use sysobjects.
Not as elegant but I'm getting the details I need out.
> Hamish Laws wrote:
> >We've working on a system that is being used to populate various tables in
> >databases on multiple servers.
> >
> >If the application is on Server 'A' and is trying to find information about
> >stored procedures on Server 'B' we're getting problems with the following
> >query
> >
> >SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> >FROM [B].[dbName].INFORMATION_SCHEMA.routines
> >ORDER BY routine_name
> >
> >
> >the error message is
> >OLE DB provider 'Darmstadtium' does not contain table
> >'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> >or the current user does not have permissions on that table.
> >
> >Does anybody have any ideas for getting this working?
> >There is a logon for Server B with the same logon that Server A is using and
> >they have System Administrator as server roles|||"Tibor Karaszi" wrote:
> I think you will have a problem with the INFORMATION_SCHEMA views. The views actually only exists in
> the master database (in 2000, in 7.0 and 2005 they are in each database), hence your problem. Try
> the system tables instead.
>
Thanks for that.
I've taken your advice and I'm getting the information out of sysobjects
without a hassle.
> "Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
> news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> > We've working on a system that is being used to populate various tables in
> > databases on multiple servers.
> >
> > If the application is on Server 'A' and is trying to find information about
> > stored procedures on Server 'B' we're getting problems with the following
> > query
> >
> > SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> > FROM [B].[dbName].INFORMATION_SCHEMA.routines
> > ORDER BY routine_name
> >
> >
> > the error message is
> > OLE DB provider 'Darmstadtium' does not contain table
> > '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> > or the current user does not have permissions on that table.
> >
> > Does anybody have any ideas for getting this working?
> > There is a logon for Server B with the same logon that Server A is using and
> > they have System Administrator as server roles.
>
>

How can we INFORMATION_SCHEMA on a different server?

Friday, February 24, 2012

How can I use one table for mutliple datasets?

Hi All,
I have a table and the dataset it populating all but four cells. These
four cells I want to populate using data from a different dataset (I
couldn;t figure out a query to do what I needed). The main dataset
returns a value I can use as a parameter in the second dataset. Is this
clear? Is this possible. I'm new to this type of reporting so please
bear with me. Thanks!
Kind regards - FredYou can create a sub report and put that in a cell of the table object. I do
this all the time. Sub reports are the only way to link two datasets
together.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<fblock@.tellurian.com> wrote in message
news:1164903187.594743.309230@.l39g2000cwd.googlegroups.com...
> Hi All,
> I have a table and the dataset it populating all but four cells. These
> four cells I want to populate using data from a different dataset (I
> couldn;t figure out a query to do what I needed). The main dataset
> returns a value I can use as a parameter in the second dataset. Is this
> clear? Is this possible. I'm new to this type of reporting so please
> bear with me. Thanks!
> Kind regards - Fred
>|||Thanks Bruce...
I see that I cannot merge cells vertically and the sub-report would
need to be two cells (one over the other). Have you a trick for this.
Also - why is it that my dataset field is "First(field_name)"... Why
the "First()"? The VS IDE was complaining of an aggregate which "sort
of" makes sense but my dataset was onle able to return one row (it
used: TOP 1).
Thanks again!
Kind regards - Fred|||Your subreport can have multiple cells. You create your subreport using the
table control with one cell over the other. You embed it into a single sell
and that cell grows vertically. However, if you are wanting the bottom cell
to line up with the second detail row you can't do it. It will expand the
cell you put it in and then your second detail row of the master will show.
You could have two subreports but remember, it is calling this subreport for
every row.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frblock" <frblock@.yahoo.com> wrote in message
news:1164915731.670399.153450@.79g2000cws.googlegroups.com...
> Thanks Bruce...
> I see that I cannot merge cells vertically and the sub-report would
> need to be two cells (one over the other). Have you a trick for this.
> Also - why is it that my dataset field is "First(field_name)"... Why
> the "First()"? The VS IDE was complaining of an aggregate which "sort
> of" makes sense but my dataset was onle able to return one row (it
> used: TOP 1).
> Thanks again!
> Kind regards - Fred
>|||frblock,
You asked why you needed to have an aggregate function in the field
expression. Perhaps this will help.
When you create a report through the report wizard, it automatically
creates a table on your report. That table is actually "bound" to a
dataset. This binding allows you to reference fields from that dataset
by just saying "=Fields!fieldName.Value". This binding also tells the
table to create 1 detail row for each datarow in the dataset.
Microsoft included the ability to reference other datasets from a table
that has already been bound to a different dataset, but there is no way
to "relate" the rows from the 2 datasets. You would need a sub-report
to do this. As a result, you have to use a scalar function to ensure
that you get only 1 row and 1 column back, and you also have to
explicitly state which dataset you want to use. The formula looks like
this: "=Sum(Fields!fieldName.Value, "Dataset1").
If you are trying to reference fields that belong to the dataset that
is already assigned to your table, in the Edit Expression window, you
should select the "Fields (Dataset1)" option. If you are trying to
reference fields from other datasets, then you choose "Datasets". The
default aggregate function for integer fields is Sum, and the default
for string fields is First. You can change the default. For example, if
you had a query that said "SELECT COUNT(*) FROM tableName", you could
use First, Min, Max, Avg, or Sum. My opinion, just stick with First.
HTH,
Josh
frblock wrote:
> Thanks Bruce...
> I see that I cannot merge cells vertically and the sub-report would
> need to be two cells (one over the other). Have you a trick for this.
> Also - why is it that my dataset field is "First(field_name)"... Why
> the "First()"? The VS IDE was complaining of an aggregate which "sort
> of" makes sense but my dataset was onle able to return one row (it
> used: TOP 1).
> Thanks again!
> Kind regards - Fred

How can i use format as HH:MM:SS from seconds within the SQL query

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.

cmd ="select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;

Please help me how to format this within the Query to display in GridView.

Hi tapan.behera,

Here is an example that does what you want:

declare @.durationintset @.duration = 1082587selectcast(@.duration / 60 / 60AS nvarchar) +'h:' +cast(@.duration / 60 % 60AS nvarchar) +'m:' +cast(@.duration % 3600 % 60AS nvarchar) +'s'

Kind regards,
Wim|||

Thanks for your suggestion.

But how can i embed your variable in my sql statement.

I don't want to use it as separate variable, i want to use it within my sql statement.

i.e Select amount,call_id,duration from my Table.

So how can i use your suggestion here, please help me.

|||

hi tapan.behera,

tapan.behera@.hotmail.com:
I don't want to use it as separate variable, i want to use it within my sql statement.

I was just making an example! When you use a select statement, that will eliminate the need of a variable.

tapan.behera@.hotmail.com:
i.e Select amount,call_id,duration from my Table.

It would be something like:

select amount, call_id,cast(duration / 60 / 60AS nvarchar) +'h:' +cast(duration / 60 % 60AS nvarchar) +'m:' +cast(duration % 3600 % 60AS nvarchar) +'s'as durationfrom myTable

Kind regards,
Wim

Sunday, February 19, 2012

How can I test a connection string?

I'm trying to convert an application from MSDE to SQL2K. I am able execute the MSDE script to populate the SQL database and I've modified the .config file to point to the new db but it appears as though the web app is not connecting.
Is there a way I can test the connection string using Visual Studio or WebMatrix?
Thanks for helping!
ScottJust use any of the data wizards in Visual Studio and go through the create connection wizard

Friday, March 30, 2012

how do I assign a string to a parameter Im passing to a select statement?

Monday, March 12, 2012

How can we INFORMATION_SCHEMA on a different server?

How can we INFORMATION_SCHEMA on a different server?

How can we INFORMATION_SCHEMA on a different server?

Friday, February 24, 2012

How can I use one table for mutliple datasets?

How can i use format as HH:MM:SS from seconds within the SQL query

Sunday, February 19, 2012

How can I test a connection string?

transfer Data From Acces

Blog Archive

About Me