retrieve Datetime: recordset

Showing posts with label recordset. Show all posts

Wednesday, March 28, 2012

Return ADO recordset to worksheet

Any help would be appreciated.
I am trying to return a recordset to a worksheet using ADO. I want to go
dsn-less.
I confident that my recordset returns data, I've tested this in Access and
can return records there. I am a new excel programmer.
I am trying to use CopyFromRecordset to return the records. When I am in
the VBA editor I choose Run Sub but no records are returned to Sheet1.
I'd like the recordset to be refreshed every time the sheet is activated.
I've created a reference to Microsoft ActiveX Data Objects 2.8 library.
Am I missing something?
Sub ImportData()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "SERVER=MYSERVER;INITIAL CATALOG=MYDATABASE;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.Open strConn
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Open "SELECT * FROM MyTable"
Sheet1.Range("A1").CopyFromRecordset rs
.Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
End SubSorry wrong newsgroup please ignore.
"Terri" <terri@.cybernets.com> wrote in message
news:e6c2n6$vlf$1@.reader2.nmix.net...
> Any help would be appreciated.
>sql

Return a resultset to XML?

Hello I am trying to pass a recordset to XML
Database: pubs
select * from titles for xml auto
but it returns me this
<titles title_id="BU1032" title="The Busy Executive's Database Guide"
type="business " pub_id="1389" price="20.0000" advance="5000.0000"
royalty="10" ytd_sales="4095" notes="An overview of available database
systems with emphasis on common business
royalty="16" ytd_sales="8780" notes="A survey of software for the naive
user, focusing on the 'friendliness' of each."
pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
Silicon Valley" type="popular_comp" pub_id="1389" pr
"psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
emotional stress in the modern world. Use of computer and nutritional aids
emphasized." pubdate="1991-06
I dont want that way
I would like more or less
<title>title1</title>
<type>type</type>
and so on.
ThanksHi
Look at the FOR EXPLICITY clause in books online and you may be able to do
what you require.
John
"Luis Esteban Valencia" wrote:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database Guide"
> type="business " pub_id="1389" price="20.0000" advance="5000.0000"
> royalty="10" ytd_sales="4095" notes="An overview of available database
> systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the naive
> user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
> Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
> ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
> emotional stress in the modern world. Use of computer and nutritional aids
> emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>|||"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database
> Guide" type="business " pub_id="1389" price="20.0000"
> advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> of available database systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the
> naive user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> loved ones from undue emotional stress in the modern world. Use of
> computer and nutritional aids emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>
select * from titles for xml auto, elements
--
Regards
JTC ^..^|||It doesnt return good. it seems to cut the strings
look at this
<titles><title_id>BU1032</title_id><title>The Busy Executive's Database
Guide</title><type>business
</type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
/price><
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> > Hello I am trying to pass a recordset to XML
> > Database: pubs
> >
> > select * from titles for xml auto
> > but it returns me this
> > <titles title_id="BU1032" title="The Busy Executive's Database
> > Guide" type="business " pub_id="1389" price="20.0000"
> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > of available database systems with emphasis on common business
> > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > naive user, focusing on the 'friendliness' of each."
> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > loved ones from undue emotional stress in the modern world. Use of
> > computer and nutritional aids emphasized." pubdate="1991-06
> >
> >
> > I dont want that way
> > I would like more or less
> > <title>title1</title>
> > <type>type</type>
> >
> > and so on.
> >
> > Thanks
> >
> >
> >
> select * from titles for xml auto, elements
> --
> Regards
> JTC ^..^|||It is only Query Analyzer. You can configure where it cut a column (max is 8000). All xml is one
column in QA.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in message
news:%23eMjeSlgFHA.3436@.tk2msftngp13.phx.gbl...
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
>> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
>> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
>> > Hello I am trying to pass a recordset to XML
>> > Database: pubs
>> >
>> > select * from titles for xml auto
>> > but it returns me this
>> > <titles title_id="BU1032" title="The Busy Executive's Database
>> > Guide" type="business " pub_id="1389" price="20.0000"
>> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
>> > of available database systems with emphasis on common business
>> > royalty="16" ytd_sales="8780" notes="A survey of software for the
>> > naive user, focusing on the 'friendliness' of each."
>> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
>> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
>> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
>> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
>> > loved ones from undue emotional stress in the modern world. Use of
>> > computer and nutritional aids emphasized." pubdate="1991-06
>> >
>> >
>> > I dont want that way
>> > I would like more or less
>> > <title>title1</title>
>> > <type>type</type>
>> >
>> > and so on.
>> >
>> > Thanks
>> >
>> >
>> >
>> select * from titles for xml auto, elements
>> --
>> Regards
>> JTC ^..^
>|||Hi
If you still have problems cutting the tags then put in dummy data values
that contain a carriage return. See http://tinyurl.com/e4rla
John
"Luis Esteban Valencia" wrote:
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribiÃ³ en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> > "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> > news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> >
> > > Hello I am trying to pass a recordset to XML
> > > Database: pubs
> > >
> > > select * from titles for xml auto
> > > but it returns me this
> > > <titles title_id="BU1032" title="The Busy Executive's Database
> > > Guide" type="business " pub_id="1389" price="20.0000"
> > > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > > of available database systems with emphasis on common business
> > > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > > naive user, focusing on the 'friendliness' of each."
> > > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > > loved ones from undue emotional stress in the modern world. Use of
> > > computer and nutritional aids emphasized." pubdate="1991-06
> > >
> > >
> > > I dont want that way
> > > I would like more or less
> > > <title>title1</title>
> > > <type>type</type>
> > >
> > > and so on.
> > >
> > > Thanks
> > >
> > >
> > >
> >
> > select * from titles for xml auto, elements
> >
> > --
> > Regards
> > JTC ^..^
>
>

Return a resultset to XML?

Wednesday, March 21, 2012

Retrieving multiple values from stored procedure: parameter and recordset

Hello,

The problem below appears to be a very common problem, but despite having tried every solution I can find offered online, the solution continues to elude me. Any tips you could offer would bemuchappreciated.

Problem: I'm successfully retrieving a recordset from a SQL Server 2000 stored procedure, but don't know how to retrieve an Output Parameter as well.

Components:

a simple Web Form with a Text Box (txtOutput) and a GridView (dgOutput). (Visual Web Developer Express 2005)
a simple stored procedure which - when executed in the Studio Output window - returns the correct output.Running [dbo].[usp_Test] ( @.Region = <NULL>, @.StartDate = <NULL> ).

State Total
-------- ----
ACT 27
NSW 26
NT 6
QLD 20
SA 44
TAS 17
VIC 28
WA 48
No rows affected.
(8 row(s) returned)
@.StartDate = 27/11/2006
@.RETURN_VALUE = 0
Finished running [dbo].[usp_Test].
the code behind the form, to call the stored proc and bind the grid to the output of the stored proc. 'Already declared and populated strConn and strSelect
Dim conn As New SqlConnection(strConn)
Dim storedProc As New SqlCommand
With storedProc
.CommandType = CommandType.StoredProcedure
.CommandText = strSelect
.Connection = conn
End With
Dim returnParam As SqlParameter
returnParam = New SqlParameter("@.StartDate", SqlDbType.DateTime, 30)
returnParam.Direction = ParameterDirection.Output
storedProc.Parameters.Add(returnParam)

Dim dr As SqlClient.SqlDataReader
conn.Open()
dr = storedProc.ExecuteReader
dgOutput.DataSource = dr
dgOutput.DataBind()
dr.Close()
conn.Close()

Situation:

the GridView successfully displays the 8-row recordsetI don't know how to display the Output Parameter ("@.StartDate") in the Text Box. Without success I've tried variations on:
txtOutput.Text = storedProc.Parameters("@.StartDate").Value.ToStringtxtOutput.Text = returnParam.Value.ToStringusing dr.NextResultIf possible, I would like to be able to display the 'number of row(s) returned', which is already being returned (as printed above).

Any assistance would be sincerely appreciated - it's been a long day of trying. Thank you for your time. Smile

- Sarah

You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test

@.StartDate datetime OUTPUT,

@.rowcount int OUTPUT

BEGIN

--Your SELECT statement here

SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime

startDate = mycommand.Parameters("@.StartDate").Value)

|||

Hi Dinakar,

Thank you for looking at my problem Smile Unfortunately the same problem is still happening.

The stored proc is already returning the output parameter with the correct value of27/11/2006(as shown in the output in my first post), but any time I try to retrieve it, it seems to have a null value.

In my VB code, I've changed my parameter creation to match your suggested code.

When I use the new DateTime variable as you suggest, and set it tostoredProc.Parameters("@.StartDate").Value, it's value is set to 1/01/0001 12:00:00 AM .

Any suggestions?

Thank you again for your time.

|||Can you post your stored proc code?|||

CREATE PROCEDURE usp_Test(
@.Region AS VARCHAR(8) = NULL, --Optional
@.StartDate DATETIME OUTPUT
)
AS

BEGIN
SET @.StartDate =DATEADD(Week, DATEDIFF(Week,0,GETDATE()), 0) --Monday of current week

SELECT Loc.LocationState AS State,
COUNT(Appt.ApartmentID) AS Total
FROM Apartment AS Appt
LEFT JOIN ReportLocation AS Loc ON Appt.LocationID = Loc.LocationID
WHERE (( Appt.InsertedOn >= @.StartDate) AND
(Appt.InsertedOn <= GETDATE()) AND
((@.Region IS NULL) OR (Loc.LocationState LIKE @.Region)) AND
(Appt.isBreak = 0) AND
(Appt.isBlock = 0) AND
(Appt.isCancelled = 0)
)
GROUP BY Loc.LocationState
ORDER BY Loc.LocationState
END
GO

|||Is there anything else it might be? I'm happy to post any of my code, which might expose the cause. Smile

|||

Allright, after a long long time, I wrote up a quick windows app. I used a datagridview and called a stored proc which returns a result set through a SELECT statement. The stored proc also has an OUTPUT parameter. I was able to retrieve both.

alter proc dbo.test_dk @.fileidint, @.rowcountint output as begin select fileid, orderid, creationdatefrom servicefilewhere fileid = @.fileidselect @.rowcount =@.@.rowcountend

And the code for the windows app is:

Dim objconAs SqlConnection objcon =New SqlConnection("server=...")Try If objcon.State = 0Then objcon.Open()Dim dsAs New DataSetDim daAs SqlDataAdapter =New SqlDataAdapter da.SelectCommand =New SqlCommand da.SelectCommand.CommandType = CommandType.StoredProcedure da.SelectCommand.CommandText ="test_dk"Dim sqlparamAs New SqlParameter("@.fileid", SqlDbType.Int) sqlparam.Value = 7035833Dim sqlparam2As New SqlParameter("@.rowcount", SqlDbType.Int) sqlparam2.Direction = ParameterDirection.Output da.SelectCommand.Parameters.Add(sqlparam) da.SelectCommand.Parameters.Add(sqlparam2) da.SelectCommand.Connection = objcon da.Fill(ds,"servicefile") DataGridView1.DataSource = ds DataGridView1.DataMember ="servicefile" TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").ValueCatch excAs Exception MsgBox(exc)Finally If objcon.State = ConnectionState.OpenThen objcon.Close()End If'objCon.Dispose()End Try

|||

That worked beautifully - you're a champion, Dinakar! Some day they'll sing songs about you.

Thank you very much for your time and help. Party!!!

|||You are welcome.|||

ndinakar:
You need to declare the @.startDate as OUTPUT parameter in the stored proc. You could also return the rowcount but that would have to be another OUTPUT parameter.

CREATE PROC dbo.usp_test
@.StartDate datetime OUTPUT,
@.rowcount int OUTPUT
AS
BEGIN
--Your SELECT statement here
SELECT ....

END

Here's how you could call an OUTPUT parameter:

'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.StartDate"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.datetime
mycommand.Parameters.Add(myParam)

Dim startDate as DateTime
startDate = mycommand.Parameters("@.StartDate").Value)

|||

I'm trying to follow your example but on this line..

Your line

TextBox1.Text = da.SelectCommand.Parameters("@.rowcount").Value

My line

lbl_ERROR.Text = da.SelectCommand.Parameters("@.RETURNError").Value;

I get a build error.. Error 1 'System.Data.SqlClient.SqlCommand.Parameters' is a 'property' but is used like a 'method'

sql

Tuesday, March 20, 2012

Retrieving from SQLServer to Word

Please,
how can I execute a SQL command into SQLServer from Word/VBA code using ADO? I want to execute a comand and work with resulting recordset.
Best regards.Please see the following link:

dbforums link (http://dbforums.com/t390641.html)

If this does not help, please respond.

Good luck !|||Thanks a lot. It works fine.

Wednesday, March 7, 2012

Retrieve Recordsets alogn with column name

Hai ,
I want to get the column names along with the rows of a table as a record
set is that possible?
Thanks,
V.BoomesshWhat programming language ?
Jens Suessmeyer.
"Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
> Hai ,
> I want to get the column names along with the rows of a table as a record
> set is that possible?
> Thanks,
> V.Boomessh|||Boomessh,
Try this and HTH.Otherwise please provide ddl and sample data.
set nocount on
create table products(Product char(2),Num_Accounts int)
insert products values('P1',20)
insert products values('P2',21)
insert products values('P3',34)
insert products values('P4',56)
insert products values('P5',12)
select col1='product',product,col2='num_account
s',num_accounts from products
drop table products
"Jens Sü?meyer" wrote:

> What programming language ?
> Jens Suessmeyer.
> "Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
>
>|||Hai,
the stated query returns like
product P1 num_accounts 20
so there are 5 products, what i need is, like this...
products, num_accounts
p1 20
p2 21
p3...
..
..
So i need 6 (5 actual values + 1 coulmn name)rows along with the column name
.
i am using VB as my programming language.
Thanks,
V.Boomessh
"ZULFIQAR SYED" wrote:
> Boomessh,
> Try this and HTH.Otherwise please provide ddl and sample data.
> set nocount on
> create table products(Product char(2),Num_Accounts int)
> insert products values('P1',20)
> insert products values('P2',21)
> insert products values('P3',34)
> insert products values('P4',56)
> insert products values('P5',12)
> select col1='product',product,col2='num_account
s',num_accounts from produc
ts
> drop table products
> "Jens Sü?meyer" wrote:
>|||I am doing a program in VB. or is it directly possible in SQL by any SP etc.
.
"Jens Sü?meyer" wrote:

> What programming language ?
> Jens Suessmeyer.
> "Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
>
>|||It shouldn't be necessary to get the column names back as a record as you
already have them if you are using VB6 or VB.NET. If you are using VB6 and A
DO,
then use the recordset object to cycle through the Fields collection retriev
ing
the name. If you are using VB.NET, then cycle through the Columns collection
on
the DataSet/DataTable to get the field names.
Thomas
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:F6A470AA-2FF8-4E33-B5D3-58668FBB14C1@.microsoft.com...
>I am doing a program in VB. or is it directly possible in SQL by any SP etc
.
> "Jens Smeyer" wrote:
>

Tuesday, February 21, 2012

Retrieve Bit type value

I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code

Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)

I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?You're not selecting what you think you're selecting. You will get a 1 or 0 back, not S00817. Do any of the fields in the record have that value? If you miss a comma in your field list, you can get goofy-looking, unexpected data. Also, when the bit comes back, you can feed it right into boolean properties. You don't have to change 1 into true, for example. Pretty handy. I'd run your SQL statement in Query Analyzer, some where outside of ASP. Actually, ADO might convert the 1 to -1. Might want to check that also. Check your query; something's up there I would suspect.|||Yes you were quite right. I was trying to read data from another field. I should have using Mydataset.tables(0).Rows(0)(13)

Thank you for your help

retrieve Datetime

Wednesday, March 28, 2012

Return ADO recordset to worksheet

Return a resultset to XML?

Return a resultset to XML?

Return a resultset to XML?

Wednesday, March 21, 2012

Retrieving multiple values from stored procedure: parameter and recordset

Tuesday, March 20, 2012

Retrieving from SQLServer to Word

Wednesday, March 7, 2012

Retrieve Recordsets alogn with column name

Tuesday, February 21, 2012

Retrieve Bit type value

Followers

Blog Archive

About Me