Showing posts with label following. Show all posts
Showing posts with label following. Show all posts

Friday, March 30, 2012

How to value parameters for sqldatasource

I set up a sqldatasource based on a stored procedure which takes one parameter. The sqldatasrouce wizard generates the following code for the parameter below. The question is how do I value the DeptID parameter on the load of the form. I tried the following code in the load of the page, but get a null reference error:

Me.SqlDataSource1.InsertParameters("DeptID").DefaultValue = Session("DeptID")

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:FDConn %>"
SelectCommand="GetTruckStatus" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="DeptID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<radG:RadGrid ID="RadGrid1" runat="server">
</radG:RadGrid>

You've set the value of an InsertParameter, but you are performing a Select, not an Insert. Anyway, the SqlDataSource offers a number of different parameter types. One of them is an <asp:SessionParameter>. Use that instead of the generic<asp:Parameter> that you are currently using. You can reconfigure the SqlDataSource to generate it automatically. Click the WHERE button when applying the Select Statement, and in the Source dropdown, choose Session.

|||

Mike,
Thanks for the information. I went back into the configure the sqldatasrouce, but if you chose to use a stored procedure, the button for selecting "Where" is grayed out. I assumed that since the code was generated from vb and from the stored procedure I selected, vb would know the type of parameter it had to generate.


Regards,
Tom

|||

Mike,

I went back into the code and used the "SelectParameter" type and all worked well. Thanks for pointing me in the right direction.

Tom

Wednesday, March 28, 2012

How to uutofit text when rendered in Excel

Hi
We are facing following issue in SQL Server Reporting Service 2000.
We are generating report in excel format using reporting service. We are
unable to format the column width and height based on the content. We tried
couple of option like CanDraw and CanShrink, but it did not work.
We would like know if this is feasible to achieve or is it limitation in
Reporting ServiceFYI: I am having similar difficulties. The report looks find in the Report
Manager, but when it's saved to Excel, text wrapping is lost.
Karen
"Omnibuzz" wrote:
> Hi
> We are facing following issue in SQL Server Reporting Service 2000.
> We are generating report in excel format using reporting service. We are
> unable to format the column width and height based on the content. We tried
> couple of option like CanDraw and CanShrink, but it did not work.
> We would like know if this is feasible to achieve or is it limitation in
> Reporting Service
>sql

Wednesday, March 21, 2012

how to use SQL Express in a shared hosting environment

All--

Please help.

I have deployed my site to my shared host and I am getting the following RTE...

An attempt to attach an auto-named database for file... ..\App_Data\CommerceDB.mdf failed

...and I cannot find the issue.

Note that I have given Full-Permissions on root folder for the IUSR account and for the ASPNET account.

The full error message is below.

Anyway, do you have an suggestions?

My web hosting provider has advertised that they do support SQL Express 2005. What exactly do I need to ask them to do in order to get this working?

Please advise.

Thank you.

--Mark Kamoski

Server Error in '/mkamoski/S16' Application.
------------------------


An attempt to attach an auto-named database for file c:\hosting\webhost4life\member\mkamoski\S16\App_Data\CommerceDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file c:\hosting\webhost4life\member\mkamoski\S16\App_Data\CommerceDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

Line 88: ''' <returns></returns>

Line 89: Public Shared Function GetCart() As ShoppingCart

Line 90: Dim rdr As IDataReader = ShoppingCartProvider.Instance.CartGetCurrentCart(GetUserName())

Line 91: Dim cart As ShoppingCart = New ShoppingCart()

Line 92: cart.Load(rdr)

Source File: c:\hosting\webhost4life\member\mkamoski\S16\App_Code\BLL\ShoppingCartManager.vb Line: 90

All--

OK. I got it. It is working.

FWIW and FYI, the site is here...

http://site291.webhost4life.com/mkamoski/S16/

...but it is just a "stock" site right now.

Anyway, here are some things that I learned.

(1). For some reason, the...

Initial Catalog=myDBName

...had to be set in Web.config for both the CommerceDB database and the ASPNET database.

(2). At my host, the database needs to have a unique name. I renamed CommerceDB to something like CommerceDB200605022058 and the same for the ASPNET database.

(3). I had to make sure that I detached the databases on my local workstation for both CommerceDB and ASPNET database BEFORE uploading.

(4). For both the CommerceDB database and the ASPNET database, I had to rename the database itself AND the actual physical files.

(5). I uploaded the *.MDF files but I could not upload the *.LDF files, at the request/suggestion of my web host and, apparently, they are correct.

After all that, it is working.

FWIW, I host athttp://www.WebHost4Life.com and I must say that I am pleased with them. It sometimes takes a little doing on my part to get things done; but, given that I am a hands-on person, I don't mind that much. What is important is that I have complete control over my site's administration, and I do with the tools they provide.

Thank you.

--Mark Kamoski


|||

mkamoski:

Hi mkamoski,

I'm having a similar issue with names.co.uk - please can you give me some more advice?

I have two empty databases set-up on the host, but these are empty - how do I copy the tables etc. from my local databses into these empty databases on the host? I am being told to us SQL Server Managment Studio Express - but I cannot see a way of copying a database from my local PC to the host.

Do you have any experience of this ? I'm going out of my mind with this one - I've been looking around for may days and still have no answer!

I'm completely new to this, so some detailed 'idiots guide' would be very welcomed.

Hoping for some advice, thanks

All--

Please help.

I have deployed my site to my shared host and I am getting the following RTE...

An attempt to attach an auto-named database for file... ..\App_Data\CommerceDB.mdf failed

...and I cannot find the issue.

Note that I have given Full-Permissions on root folder for the IUSR account and for the ASPNET account.

The full error message is below.

Anyway, do you have an suggestions?

My web hosting provider has advertised that they do support SQL Express 2005. What exactly do I need to ask them to do in order to get this working?

Please advise.

Thank you.

--Mark Kamoski

Server Error in '/mkamoski/S16' Application.
------------------------


An attempt to attach an auto-named database for file c:\hosting\webhost4life\member\mkamoski\S16\App_Data\CommerceDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file c:\hosting\webhost4life\member\mkamoski\S16\App_Data\CommerceDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

Line 88: ''' <returns></returns>

Line 89: Public Shared Function GetCart() As ShoppingCart

Line 90: Dim rdr As IDataReader = ShoppingCartProvider.Instance.CartGetCurrentCart(GetUserName())

Line 91: Dim cart As ShoppingCart = New ShoppingCart()

Line 92: cart.Load(rdr)

Source File: c:\hosting\webhost4life\member\mkamoski\S16\App_Code\BLL\ShoppingCartManager.vb Line: 90

|||

MDH:

I'm having a similar issue with names.co.uk - please can you give me some more advice?

I have two empty databases set-up on the host, but these are empty - how do I copy the tables etc. from my local databses into these empty databases on the host? I am being told to us SQL Server Managment Studio Express - but I cannot see a way of copying a database from my local PC to the host.

Do you have any experience of this ? I'm going out of my mind with this one - I've been looking around for may days and still have no answer!

I'm completely new to this, so some detailed 'idiots guide' would be very welcomed.

Hoping for some advice, thanks

First, I must say that I am also new to SQL Server 2005 Express too, so please listen to what I say with care and with caution and with skepticism and keep my disclaimer in mind-- "beware, I don't really know what I am doing here, but I am going to TRY something".

:-)

Now, to copy database objects (not data, just the objects such as tables, views, and SPs) one can use the management tool GUI to create a script of the objects. Then, just run the scripts on the empty database and you are done. I did this for my stuff and it worked great. I also had a script of "seed data" the initializes the core data in the database. This is, I think, a great way to go during development because one can always re-initialize the database to a "clean" state, which is essential for accurate testing.

For details on the GUI tool, look for this...

SQL Server 2005 Express Edition with Advanced Services

...which should be at this link...

http://msdn.microsoft.com/vstudio/express/sql/download/

...which has...

"SQL Server Management Studio Express, a graphical management tool based on SQL Server Management Studio that makes it easy to manage and administer SQL Server Express databases"

...so that should help.

Also at that link, get the Books Online (BOL), as they will provide a lot of detail to you on-demand.

Now, as far as migrating both objects AND data, I am a bit stuck. When I tried to do this, I had an old version of the management tool (the "technonolgy preview" edition). It crashed a lot and I could not figure out how to do it. That was sad because it is very easy in SQL Server 2000. That's not to say that it cannot be done-- I am just saying that I could not do it in the 10 minutes that I spent on the issue.

As an alternate approach, one could simply backup the complete "good" database and then restore the backup to the target location. Unfortunately, I also tried this in the "technology preview" edition and could not get it to work. Again, that's not to say that it cannot be done-- I am just saying that I could not do it in the 10 minutes that I spent on the issue.

The other way is to simply physically move a copy of the "good" MDF file itself to the target location, as I note in the numbered steps to the solution that I used, above. Those steps were all necessary for me to get it to work.

That's all I have for now. Unfortunately, I cannot give you more details right now because I am in a location that is still on .NET 1.1; therefore, I cannot experiment with SQL Server 2005 Express. When I get to my home development location where I can try out SQL Server 2005 Express again, I may have forgotten this thread; so, I am giving you what I have now, FWIW.

HTH.

Thank you.

--Mark Kamoski

|||Many thanks, i'll give this a try :-)|||

Hi MDH,

Did you ever manage to get this working? I am trying to use SQL Server Express to copy tables from my local server to the remote server. I know it's easy in the full version because you have DTS, but it seems to be impossible in the Express edition.

Cheers.

How to use Service Pack?

Hi
I've installed Sql server 2000 in server station
and now I want help me for following quations?
1 - as I studied Microsoft site , I found 3 service pack
for it,is that right? 2 - how should I install SP1,SP2,Sp3?
(I heared it's neccessary to uninstall SQL Server 2000 and then install it
and then install the SP1,...is that right?)
I'd be thankful if you direct me
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/SP3a is cumulative, installing it will install everything needed from
previous service packs.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"R-M" <m_r1824@.yahoo.co.uk> wrote in message
news:oprzdtxhxmy4e8ci@.msnews.microsoft.com...
> Hi
> I've installed Sql server 2000 in server station
> and now I want help me for following quations?
> 1 - as I studied Microsoft site , I found 3 service pack
> for it,is that right? 2 - how should I install SP1,SP2,Sp3?
> (I heared it's neccessary to uninstall SQL Server 2000 and then install it
> and then install the SP1,...is that right?)
> I'd be thankful if you direct me
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Hi,
Please go ahead and install SP3a directly for SQL 2000.
Thanks
Hri
MCDBA
"R-M" <m_r1824@.yahoo.co.uk> wrote in message
news:oprzdtxhxmy4e8ci@.msnews.microsoft.com...
> Hi
> I've installed Sql server 2000 in server station
> and now I want help me for following quations?
> 1 - as I studied Microsoft site , I found 3 service pack
> for it,is that right? 2 - how should I install SP1,SP2,Sp3?
> (I heared it's neccessary to uninstall SQL Server 2000 and then install it
> and then install the SP1,...is that right?)
> I'd be thankful if you direct me
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

How to use SCOPE_IDENTITY() in 4-layer architecture

I have the following stored procedure:

INSERT INTO MyTable( Value1, Value 2)

VALUES( @.Value1, @.Value2)

SELECT SCOPE_IDENTITY()

How do I put this sp in the DAL typed dataset, so I can get the Identity value in the Business Layer?

From the DAL execute a query and use cmd.ExecuteScalar() this will return the identity field...

Please specify more details if this won't help you

|||

Hi,

You can use a SqlDataAdapter to execute this stored procedure as a common SELECT query. The result set will be filled into a DataSet. And the first row, first column of the first table will be the scope identity.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Monday, March 19, 2012

How To Use Return Value from SqlDataSource

I have the following code in my page:

<asp:SqlDataSourceID="SqlHolidayDateRange"runat="server"ConnectionString="(OurConnectionString)"

SelectCommand="CountHolidays"SelectCommandType="StoredProcedure">

<SelectParameters><asp:ControlParameterControlID="WeekEndingDatePicker"Name="EndDate"PropertyName="SelectedDate"/><asp:ParameterDirection="ReturnValue"Name="RowCount"Type="Int32"/></SelectParameters></asp:SqlDataSource>

I need to place RowCount, the returned integer value from the stored procedure CountHolidays into a field on the web page. How in the world do I access that data?

Everything I've been able to find works if I'm defining my own command object and writing this thing from scratch. What I - and you -have to work with is what's above.

Hi,

Add OnSelected="SqlHolidayDateRange_Selected" to SqlDataSource then you can get return value using

Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);

Hope this helps.

|||

Like this:

voidSqlHolidayDateRange_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RowCount = Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);

//add your code.......

}

|||I'm sorry, but I'mreally new at this. Neither of your answers make much sense to me. Where do I add those things? In the page, in the codebehind, or what?|||

Doesn't matter.

Add OnSelected="SqlHolidayDateRange_Selected" in your page:

<asp:SqlDataSourceID="SqlHolidayDateRange"runat="server"ConnectionString="(OurConnectionString)"

SelectCommand="CountHolidays"SelectCommandType="StoredProcedure">

OnSelected="SqlHolidayDateRange_Selected"

<SelectParameters>

<asp:ControlParameterControlID="WeekEndingDatePicker"Name="EndDate"PropertyName="SelectedDate"/>

<asp:ParameterDirection="ReturnValue"Name="RowCount"Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

And add the following to you code-behinde:

void SqlHolidayDateRange_Selected(object sender, SqlDataSourceStatusEventArgs e) {int RowCount = Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);//add your code.......}

Then you can display RowCount .

You can take a look atSqlDataSource in QuickStart Tutorials.

How to use Reportviewer to open a SSRS Report

I'm trying to open a report using the following code:

Dim strReportPathAs String Select Case Request.QueryString("Report")Case"Report1" strReportPath ="/reportfolder/report1&val1=" & Request.QueryString("1") &"&=" & Request.QueryString("datefrom") &"&dateto=" & Request.QueryString("dateto") &"&severity=" & Request.QueryString("severity") &""Case"Report2" strReportPath ="/reportfolder/report2&val1=" & Request.QueryString("1") &"&=" & Request.QueryString("datefrom") &"&dateto=" & Request.QueryString("dateto") &"&severity=" & Request.QueryString("severity") &""Case"Report3" strReportPath ="/reportfolder/report3&val1=" & Request.QueryString("1") &"&=" & Request.QueryString("datefrom") &"&dateto=" & Request.QueryString("dateto") &"&severity=" & Request.QueryString("severity") &""End Select With ReportViewer1 .ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote .ServerReport.ReportServerUrl =New Uri(http://myserver/reportserver") .ServerReport.ReportPath = strReportPath .ServerReport.Refresh()End With

I get the following error:

The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.The request failed with HTTP status 404: Not Found.

I can run the reports without any issues on the report server. I'm using Visual Studio 2005.

Can anyone see where I'm going wrong?

Thanks in advance.

I've found that my problem is that I'm using SQL2000 and the Reportviewer only works with SQL2005.

How to use reporting services with data mining ?

Hi ,

How can I use reporting services to get reports out of analysis Services Database ?

I found following on one site, but I tried to look for Analysis services database on the server I am deploying the model , but I am not able to find it.

'To use Reporting Services with data mining, you simply create a Reporting Services project with the BI Dev Studio and specify an Analysis Services database as your data source. Depending on the contents of the database, you will be presented with either an OLAP or Data Mining query builder as in Figure 3.23. You can always switch between the two modes by clicking the Switch '

thanks,

Vikas

Hi

Please try the following:

1. In your report project, right click the Shared Data Sources and select "Add New Shared Data Source"

2. In the Type box, select "Microsoft SQL Server Analysis Services"

3. Click on the "Edit" button. In the connection dialog, specify the Analysis Server name (Optionally instance name) in the Server name text box.

4. Drop the database name combo box and select the database name where your Data Mining models have been deployed.

5. Click OK to create a shared data source

When creating a new report, select this data source and select the "Query Builder". You should see a DMX query designer where you'll be able to create a dataset for your report.

Hope this helps. Please reply if you have any additional questions

Thanks

Shuvro

|||


Hi Shuvro,

I did everything you specified. But when I go to query builder window I do not see any mining model there. My mining project is fine. It shows me all models , lift charts etc in Analysis Services , which indicates the the SSAS Database I am using has all models etc. Why nothing is being shown whne I open the DB is RS ?

Thanks,

Vikas

|||

I think the UI is a little confusing. You'll see a empty Mining Model window with a button "Select Model...". If you click the button and navigate to the DB->Mining Structure->Mining Model and hit OK, the query window should get populated with the model and the columns

If this does not work, can you please send me a screen shot of your query window to (shuvrom at microsoft.com). at=@.

Thanks

|||Hi Shuvro,

Your answer is correct. However, that way I'm still unable to get the chart of decision tree using the Reporting Service (and Report Builder). How can I display the chart of decision tree, chart of time-series and clusters in the web? Your answer can only be used to display numbers that are the result of prediction-query or simple-query; not chart or picture in any way.

Thank you,

Bernaridho|||Currently there isn't any built-in way to display the graphical results of the DM viewers in a report. A sample that has thin client versions of the decision tree, classification and discrimination charts is provided if you install all of the samples included with the SQL Server product. You can also see these samples in action at www.sqlserverdatamining.com in the Live Samples section.|||Hi Jamie,

I've tried searching what you mean, but I have not found it.
Do you mean the thin client viewer? If that's what you mean, then I have problem downloading it yet I can try again later.

However, if you also mean one-or-more post(s) in sqlserverdatamining.com, could you please give the URL (or specific date) of that post(s)?

Thank you,

Bernaridho|||

The smples are included witht he product, but they need to be explicitly installed. To install, you need to click the advanced button on the page where you select components and drill down into the documentation and samples node.

The like I was referring to was http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/52.aspx, where it shows the viewers in action.

How to use reporting services with data mining ?

Hi ,

How can I use reporting services to get reports out of analysis Services Database ?

I found following on one site, but I tried to look for Analysis services database on the server I am deploying the model , but I am not able to find it.

'To use Reporting Services with data mining, you simply create a Reporting Services project with the BI Dev Studio and specify an Analysis Services database as your data source. Depending on the contents of the database, you will be presented with either an OLAP or Data Mining query builder as in Figure 3.23. You can always switch between the two modes by clicking the Switch '

thanks,

Vikas

Hi

Please try the following:

1. In your report project, right click the Shared Data Sources and select "Add New Shared Data Source"

2. In the Type box, select "Microsoft SQL Server Analysis Services"

3. Click on the "Edit" button. In the connection dialog, specify the Analysis Server name (Optionally instance name) in the Server name text box.

4. Drop the database name combo box and select the database name where your Data Mining models have been deployed.

5. Click OK to create a shared data source

When creating a new report, select this data source and select the "Query Builder". You should see a DMX query designer where you'll be able to create a dataset for your report.

Hope this helps. Please reply if you have any additional questions

Thanks

Shuvro

|||


Hi Shuvro,

I did everything you specified. But when I go to query builder window I do not see any mining model there. My mining project is fine. It shows me all models , lift charts etc in Analysis Services , which indicates the the SSAS Database I am using has all models etc. Why nothing is being shown whne I open the DB is RS ?

Thanks,

Vikas

|||

I think the UI is a little confusing. You'll see a empty Mining Model window with a button "Select Model...". If you click the button and navigate to the DB->Mining Structure->Mining Model and hit OK, the query window should get populated with the model and the columns

If this does not work, can you please send me a screen shot of your query window to (shuvrom at microsoft.com). at=@.

Thanks

|||Hi Shuvro,

Your answer is correct. However, that way I'm still unable to get the chart of decision tree using the Reporting Service (and Report Builder). How can I display the chart of decision tree, chart of time-series and clusters in the web? Your answer can only be used to display numbers that are the result of prediction-query or simple-query; not chart or picture in any way.

Thank you,

Bernaridho|||Currently there isn't any built-in way to display the graphical results of the DM viewers in a report. A sample that has thin client versions of the decision tree, classification and discrimination charts is provided if you install all of the samples included with the SQL Server product. You can also see these samples in action at www.sqlserverdatamining.com in the Live Samples section.|||Hi Jamie,

I've tried searching what you mean, but I have not found it.
Do you mean the thin client viewer? If that's what you mean, then I have problem downloading it yet I can try again later.

However, if you also mean one-or-more post(s) in sqlserverdatamining.com, could you please give the URL (or specific date) of that post(s)?

Thank you,

Bernaridho|||

The smples are included witht he product, but they need to be explicitly installed. To install, you need to click the advanced button on the page where you select components and drill down into the documentation and samples node.

The like I was referring to was http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/52.aspx, where it shows the viewers in action.

How to use OPENXML to retrieve detail lineitems

Hi there,
I was trying to retrieve all LineItems from the following query. However,
if I execute the following query, it only returns the First item. How could
I modify the OPENXML synatax so it will also return the Second item and Thir
d
item in one select statement like this:
VINET 1996-07-04 00:00:00.000 11 12 First item
VINET 1996-07-04 00:00:00.000 11 12 Second item
VINET 1996-07-04 00:00:00.000 11 12 Third item
VINET 1996-07-04 00:00:00.000 42 10 NULL
LILAS 1996-08-16 00:00:00.000 72 3 NULL
What I would like to do is to insert all fields on the XML doc including
these LineItem into a database table.
Thanks.
Abel Chan
-- ******************
-- ****** Query ******
-- ******************
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
<LineItem>Second item</LineItem>
<LineItem>Third item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
EXEC sp_xml_removedocument @.idocAbel
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10">
<LineItem>Second item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="12">
<LineItem>Third item</LineItem>
</OrderDetail>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- <LineItem>Second item</LineItem>
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
EXEC sp_xml_removedocument @.idoc
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:70D61777-6563-4323-B97C-310113BD2321@.microsoft.com...
> Hi there,
> I was trying to retrieve all LineItems from the following query. However,
> if I execute the following query, it only returns the First item. How
> could
> I modify the OPENXML synatax so it will also return the Second item and
> Third
> item in one select statement like this:
> VINET 1996-07-04 00:00:00.000 11 12 First item
> VINET 1996-07-04 00:00:00.000 11 12 Second item
> VINET 1996-07-04 00:00:00.000 11 12 Third item
> VINET 1996-07-04 00:00:00.000 42 10 NULL
> LILAS 1996-08-16 00:00:00.000 72 3 NULL
>
> What I would like to do is to insert all fields on the XML doc including
> these LineItem into a database table.
> Thanks.
> Abel Chan
> -- ******************
> -- ****** Query ******
> -- ******************
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> <LineItem>Second item</LineItem>
> <LineItem>Third item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> EXEC sp_xml_removedocument @.idoc
>
>|||Hi Uri,
Thanks to your prompt reply. However, the xml document I got has multiple
<LineItem> under each <OrderDetail>. I wish each <OrderDetail> has only one
<LineItem>.
Is there a way to extract all the <LineItem> out? Even it is in a plain xml
text. With that, I can use another OPENXML to parse it.
Thanks Uri.
Abel
"Uri Dimant" wrote:

> Abel
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10">
> <LineItem>Second item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="12">
> <LineItem>Third item</LineItem>
> </OrderDetail>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
>
> </Order>
> </Customer>
> </ROOT>'
> -- <LineItem>Second item</LineItem>
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> EXEC sp_xml_removedocument @.idoc
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:70D61777-6563-4323-B97C-310113BD2321@.microsoft.com...
>
>|||Hello,
I test the following code and it works fine on my side:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
<LineItem>Second item</LineItem>
<LineItem>Third item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail/LineItem')
WITH (
CustomerID varchar(10) '//CustomerID',
OrderDate datetime '//OrderDate',
ProdID int '//@.ProductID',
Qty int '//@.Quantity',
LineItem VARCHAR(50) 'text()')
union
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
order by customerID desc
EXEC sp_xml_removedocument @.idoc
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||That is exactly want I need. Union does the trick. :> Thanks so much Soph
ie.
BTW, I came up with an alternative while I was waiting for your reply.
I setup a #temp table by inserting all the LineItems. I also insert
customerId and Orderdate so I can find the LineItem with these two
identifiers. Then I use cursor to loop through the #temp. It works but
compare to yours, your solution is way cleaner and way easier to handle any
exception errors I will replace mine with your code.
Thanks again.
Abel Chan
"Sophie Guo [MSFT]" wrote:

> Hello,
> I test the following code and it works fine on my side:
>
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> <LineItem>Second item</LineItem>
> <LineItem>Third item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
>
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail/LineItem')
> WITH (
> CustomerID varchar(10) '//CustomerID',
> OrderDate datetime '//OrderDate',
> ProdID int '//@.ProductID',
> Qty int '//@.Quantity',
> LineItem VARCHAR(50) 'text()')
> union
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> order by customerID desc
>
> EXEC sp_xml_removedocument @.idoc
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello,
I am glad to hear that the informaion is helpful. Have a nice day!
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, March 7, 2012

How to use Convert date statement in cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).V

Hi

I am using SQL 2005, VB 2005

I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works fine

string insertSQL;

insertSQL ="INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@.ProjectID, @.TypeofEntryID, @.PriorityID ,@.Title, @.Area)";

cmdInsert SqlCommand;

cmdInsert=new SqlCommand(insertSQL,conn);

cmdInsert.Parameters.Add("@.ProjectID",SqlDbType.Varchar).Value=ProjectID.Text;

My query is how to detail with dates my previous code was

insertSQL +="convert(datetime,'" +DateTime.Now.ToString("dd/MM/yy") +"',3), '";

I tried the code below but the record doesn't save?

string date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Could someone point to me in the right direction?

Thanks in advance

yazzy:


string insertSQLstring date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Why don't you just directly pass DateTime.Now to the @.Date parameter (SqlDbType.DateTime)?

|||

Sorry,

I am having a hard time trying to figure out what it is that you are trying to do. Convert(datetime,@.Date,3) does NOTHING. @.Date is already a datetime, and the third parameter isn't valid for datetime to datetime ?conversions?.

I think you are trying to store a date into a datetime column? Or is the date field a varchar?

If it's a datetime, then change:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, @.Date,@.IDStaff)";

andchange:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

to:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = Now.Date

*OR* change your insert to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, DATEADD(d,0,DATEDIFF(d,0,getdate())),@.IDStaff)";

and remove the Date parameter completely (Which is the method I prefer, since I use the SQL Server's clock exclusively).

|||

Hi Motley

I am trying to store a date into a datetime column. The thrid parameter is to store the date in 'dd/MM/yy' formate (i.e UK date)

Because I have to store date in the above formate, it is causing when I try to insert/update a record.

Sorry I had not included this information.

Speak soon

How to use comma separated value list in the where clause?

How to use comma separated value list in the where clause?

I would like to do something like the following (Set voted = true for all rows in tblVoters where EmpID is in the comma separated value list).

update tbl_Voters

set voted = true

whereEmpID in @.empIdsCsv

Where, @.empIdsCsv = ’12,23,345,’ (IDs of the employees)

Since the above is not possible I have done the following dynamic query:

-- Convert the comma separated values to conditional statement like EmpID = {id} or EmpliD = {id}…

set @.empIdsCsv = 'EmpID=' + substring(@.empIdsCsv , 0, len(@.empIdsCsv ))-- Remove trailing comma

set @.empIdsCsv = replace(@.empIdsCsv , ',', ' or EmpID=')

declare @.markVoters varchar(8000)

set @.markVoters = '

update tbl_Voters

setvoted = true

where ’ + @.empIdsCsv

--Execute the dinamic query

exec (@.markVoters)

The above code generates the following dynamic query:

update tbl_Voters

setvoted = true

where

EmpID= 12 or EmpID=23 or EmpID=345

The obvious drawback here is the performance and the limitation of the dynamic query length (8000 chars).

Can someone suggest a better solution with the ability to use comma seperated values in the where clause?

there is the IN clause however I somehow get the feeling that will not help performance that much. The syntax is
select columns from tablename where EMplID IN (12, 23, 356, ....)

Hope this helps
|||

Please take a look at the link below that describes various techniques on how to pass a list of values from client to SQL Server.

http://www.sommarskog.se/arrays-in-sql.html

Friday, February 24, 2012

How to use Case or If statement in Inner Join ?


Hi All,

I am in need for to write the following query for a stored procedure in SQL Server 2000. Please observe the T-SQL code first.
Please disregard the numbers and Product Numbers, they are not the correct data. I used them for ease of understanding. But the query is identical.

Code Block

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
CASE WHEN @.vchSubscription = 'Weekly' THEN
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
AND C.dtInsertDate > = @.dtIssueDate

CASE WHEN @.vchSubscription = 'Monthly' THEN

INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
AND C.dtInsertDate > = @.dtIssueDate

END

GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8


my requirement is join the same table, but depending on the subscription type i have to join to different product numbers.
I hope you understand. I have been trying this since yesterday, but no luck.

Any help would be greatly appreciated.Maybe this:

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
INNER JOIN ProductMaster PM
ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR
( @.vchSubscription = 'Weekly' AND PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
OR
( @.vchSubscription = 'Monthly' AND PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
)
AND C.dtInsertDate > = @.dtIssueDate
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8|||

MadhuBabu,

You can use different statements or dynamic sql. You can not use "case" to control program flow. It is a function, so it has to return something when used.

if @.vchSubscription = 'Weekly'

select ...

else

if @.vchSubscription = 'Monthly'

select ...

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Hi Dale J Smile

Thank you very much for the answer. Well, just before 30 minutes I figured another less impressive method which is agreed on. following is the code for that

Code Block

DECLARE @.dtIssueDate DATETIME
SET @.dtIssueDate = '2007-10-01'

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'week1', 'week2', .. 'week4' ,
'Jan', 'Feb', ... , 'Dec'
)
)
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8

/*
Remove Weekly Subscribers
*/
IF @.vchJournal = 'Monthly'
BEGIN
DELETE FROM #Tble WHERE iOwnerId IN (
SELECT DISTINCT C.iOwnerId FROM CustomerProduct C
INNER JOIN ProductMaster PM ON pm.chProductNumber = C.chProductNumber
AND PM.chProductNumber IN ('week1', 'week2', .., 'week4')
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
)
END
ELSE IF @.vchJournal = 'Weekly' /* Remove Monthly Subscribers */
BEGIN
DELETE FROM #Tble WHERE iOwnerId IN (
SELECT DISTINCT C.iOwnerId FROM CustomerProduct C
INNER JOIN ProductMaster PM ON pm.chProductNumber = C.chProductNumber
AND PM.chProductNumber IN ('Jan', 'Feb', ... , 'Dec')
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
)
END


But for sure, your code is more optimal, and am implementing it now...
Many Many Thanks.