How To Retrieve Value From DataReader Which Come From Function ?

May 5, 2008

Hi...

I made some code which is to return DataReader.

And then I want to use it in other procedure. But I don't know how to extract value from Reader.

Public Function GetMyProfile(ByVal uid As string) As SqlDataReader

Using conn As New SqlConnection(ConnString)

.

.

Dim Reader As SqlDataReader = cmd.ExecuteReader()
Return Reader.RecordsAffected
cmd.Dispose()
End Using
End Function

Sub Button_Click()

dim dr as SqlDataReader

dr=GetMyProfile(ByVal uid As string)

....Is it right way ?

End Sub

View 4 Replies

Select Max(xxx) Function: How Do I Retrieve The Value??

Jun 9, 2006

I have a sql query where i want to find the highest number in a column. I believe i am querying for the data properly, but i think my problem is that i don't know how to retrieve that information in code. The error message i get is this:"Conversion from type 'DBNull' to type 'Integer' is not valid"Here is my code:------------------------------------------------------------- queryString = "SELECT MAX(SortOrder) as HighestNumber " & _ "FROM uQI_Questions WHERE (SurveyID = @SurveyID)"........ more code in between ....... ' Make sure there is 1 and only 1 record returned. If ds.Tables(0).Rows.Count = 1 Then ' retrieve the number and add 1 to it. NextNumber = ds.Tables(0).Rows(0)("HighestNumber") NextNumber += 1 Else ' Else it must be the FIRST record so return '1'. NextNumber = 1 End If-------------------------------------------------------------The line that generates the error is "NextNumber = ds.Tables(0).Rows(0)("HighestNumber")".Any suggestions?

View 1 Replies View Related

Table Function To Retrieve File Properties

Mar 25, 2008

Here's a table function that returns a table variable containing various file properties (creation date, size, etc.). The one limitation to remember is that it won't work with files on mapped drives; you have to use the full \<Server><Share><Folder1..Folder n><Filename> syntax. Still, it's a pretty useful thing to have around. :)

/****************************************************************************/
CREATE FUNCTION fnc_GetFileProps

(@FileName VARCHAR (1024))

RETURNS @Results TABLE (
ErrorCode TINYINT DEFAULT (0),
PropName VARCHAR (255),
PropValue SQL_VARIANT
)

AS
BEGIN
DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Message VARCHAR (8000)
DECLARE @ErrorSource VARCHAR (255)
DECLARE @ErrorDesc VARCHAR (255)
DECLARE @INT INT
DECLARE @VARCHAR VARCHAR (1024)
DECLARE @DATETIME DATETIME
DECLARE @BIGINT BIGINT

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FS, @ErrorSource OUT, @ErrorDesc OUT

INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, @ErrorSource, @ErrorDesc)

RETURN
END

EXEC @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT, @Filename
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FS, @ErrorSource OUT, @ErrorDesc OUT

INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, @ErrorSource, @ErrorDesc)

RETURN
END

EXEC @OLEResult = sp_OAGetProperty @FileID, 'Attributes', @INT OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Attributes', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Attributes', @INT)

EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateCreated', @DATETIME OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'DateCreated', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('DateCreated', @DATETIME)

EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateLastAccessed', @DATETIME OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'DateLastAccessed', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('DateLastAccessed', @DATETIME)

EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateLastModified', @DATETIME OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'DateLastModified', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('DateLastModified', @DATETIME)

EXEC @OLEResult = sp_OAGetProperty @FileID, 'Name', @VARCHAR OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Name', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Name', @VARCHAR)

EXEC @OLEResult = sp_OAGetProperty @FileID, 'Path', @VARCHAR OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Path', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Path', @VARCHAR)

EXEC @OLEResult = sp_OAGetProperty @FileID, 'ShortPath', @VARCHAR OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'ShortPath', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('ShortPath', @VARCHAR)

EXEC @OLEResult = sp_OAGetProperty @FileID, 'Size', @BIGINT OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Size', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Size', @BIGINT)

EXEC @OLEResult = sp_OAGetProperty @FileID, 'Type', @VARCHAR OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Type', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Type', @VARCHAR)

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

RETURN
END
/****************************************************************************/

I geek, therefore I am

View 1 Replies View Related

(JDBC) Can You Retrieve A Resultset From A Table-value Function?

Jan 31, 2008

I suppose that this could also be called: Can you retrieve a parameterized view with JDBC? Either accomplishes the same basic task.

I have looked in the documentation, and there really isn't much that talks about JDBC and functions - just stored procedures - which do not returned a CURSOR type according to Microsoft's documentation.

So far, I can retrieve scaler type data in both procedures and functions, and retrieve data sets through executeQuery with a provided sql query. How to return a resultset in Java using procedure or function has still eluded me. (<sarcasm intended> And I thought Java could do just about anything. Who would've thought...)

Any ideas would be greatly appreciated. Resources, books, etc. Google hasn't offered much help with SQL Server and JDBC.

Thanks,

Ken

View 1 Replies View Related

Retrieve Data For Working Days Using Date Function

Aug 22, 2007

Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?

Thanks...

View 2 Replies View Related

Retrieve The Version Of SQL Server From Within A User Defined Function

Sep 12, 2006

EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.

I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?

View 4 Replies View Related

SQL Server 2012 :: Patindex Function - Cannot Retrieve Or Extract Single Numeric Value

Jul 17, 2015

I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.

"2 8 7"
"2 8"
"2"
"22"
"3 2 8"

I have tried the following

Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,

Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,

Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),

View 5 Replies View Related

How To Retrieve The Char(1) Column From SQL Server With Dbbind() Function In Windows C Programming?

May 10, 2007

I have used the following Windows C codes to retrieve records from the bus_newjob table in SQL server:

==========================================================

// construct command buffer to be sent to the SQL server
dbcmd( dbproc, ( char * )"select job_number, job_type," );
dbcmd( dbproc, ( char * )" disp_type, disp_status," );
dbcmd( dbproc, ( char * )" start_time, end_time," );
dbcmd( dbproc, ( char * )" pickup_point, destination," );
dbcmd( dbproc, ( char * )" veh_plate, remark," );
dbcmd( dbproc, ( char * )" customer, cust_contact_person," );
dbcmd( dbproc, ( char * )" cust_contact_number, cust_details" );
dbcmd( dbproc, ( char * )" from bus_newjob" );
dbcmd( dbproc, ( char * )" where disp_status = 0" );
dbcmd( dbproc, ( char * )" order by job_number asc" );

result_code = dbsqlexec( dbproc ); // send command buffer to SQL server

// now check the results from the SQL server
while( ( result_code = dbresults( dbproc ) ) != NO_MORE_RESULTS )
{
if( result_code == SUCCEED )
{
memset( ( char * )&disp_job, 0, sizeof( DISPATCH_NEWJOB ) );
dbbind( dbproc, 1, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_number );
dbbind( dbproc, 2, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_type );
dbbind( dbproc, 3, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_type );
dbbind( dbproc, 4, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_status );
dbbind( dbproc, 5, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.start_time );
dbbind( dbproc, 6, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.end_time );
dbbind( dbproc, 7, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.pickup_point );
dbbind( dbproc, 8, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.destination );
dbbind( dbproc, 9, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.veh_plate );
dbbind( dbproc, 10, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.remark );
dbbind( dbproc, 11, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.customer );
dbbind( dbproc, 12, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_person );
dbbind( dbproc, 13, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_number );
dbbind( dbproc, 14, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_details );

// now process the rows
while( dbnextrow( dbproc ) != NO_MORE_ROWS )
{
new_job = malloc( sizeof( DISPATCH_NEWJOB ) );
if( !new_job )
return( 0 );
memcpy( ( char * )new_job, ( char * )&disp_job, sizeof( DISPATCH_NEWJOB ) );
append_to_list( &Read_Job_List, new_job );
}
}
else
{
sprintf( str, "Results Failed, result_code = %d", result_code );
log_str( str );
break;
}
==========================================================

where the job_type columIn is of the char(1) type, NTBSTRINGBIND is the vartype argument in the dbbind() function.

However, what I have gotten is nothing more than a null string from the job_type column. I have alternatively changed the vartype argument to STRINGBIND, CHARBIND and even INTBIND, but the results are the same.

Who can tell me the tricks to retrieve a char(1) column from SQL server?

View 1 Replies View Related

Query To Retrieve Max Price Without Using Max Function And Sub Query

Mar 5, 2014

ItemIDItemNamePrice

1Item1120.00
2Item2234.00
3Item3250.00
4Item4300.00
5Item5300.00
6Item6290.00
7Item7170.00
8Item890.00
9Item9170.00

the above is the existing table and i need a query to retrieve max price with out using max function and sub query

View 6 Replies View Related

Datareader Destination As Source For Other Datareader Source ?

Aug 30, 2006

HI!

as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?

how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?

Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?

THANKS, HANNES

View 7 Replies View Related

Help With Datareader

Jun 8, 2007

Hey guys, whats an easy way to pass a value into a stored procodure?
I tried the code below but I keep on getting a "Procedure 'sp_InsertData' expects parameter '@gpiBatchNo', which was not supplied." error. My stored proc basically gets inserts the passed variable into a databaseSqlConnection sqlSecConnection = new SqlConnection(sqlPriConnString);SqlCommand sqlSecCommand = new SqlCommand();
sqlSecCommand.Connection = sqlSecConnection;
sqlSecCommand.CommandText = "sp_InsertData";sqlSecCommand.CommandType = CommandType.StoredProcedure;sqlSecCommand.Parameters.Add("@gpiBatchNo", SqlDbType.NVarChar) ;
sqlSecConnection.Open();int returntype = sqlSecCommand.ExecuteNonQuery();
sqlSecConnection.Close();

View 2 Replies View Related

How Can I Use Datareader

Oct 20, 2007

This code is currently loading my DataGridView
How can i change this to use the Datareader

Dim myConnection As SqlConnection = New SqlConnection("Data Source=ANTEC30SQLEXPRESS;Initial Catalog=test;Integrated Security=True;Pooling=False")

Dim myCmd As SqlCommand = myConnection.CreateCommand()

myConnection.Open()

myCmd.CommandType = Data.CommandType.Text

myCmd.CommandText = "Select * From tblParts"

Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(myCmd)

Dim myDataSet As DataSet = New DataSet()

myDataAdapter.Fill(myDataSet)

DataGridView1.DataSource = myDataSet.Tables(0)

View 7 Replies View Related

DataReader Access

Feb 22, 2007

Hi,
I am facing a problem to access datareader... actually i want to get data on lables from datareader. actually i am having one table having only one column and i hav accessed all data into datareader but the problem is that i just want to get data row by row...

For example there are four labels Label1, Label2, Label3, Label4
and want to print the data from datareader on to thease labels....

plz do reply... i am in trouble

View 1 Replies View Related

DataReader And DataAdapter

Feb 28, 2007

Hi, What is the difference b/w sqldatareader and sqldataadapter? For what purpose are they used in a database connection & how do they differ from each other? Pls explain me in detail.Regards Vijay.

View 1 Replies View Related

Dataset Or Datareader?

Jun 20, 2007

i need help to know what is the best practice
i have a stored proc which returns 4 different resultselts
will that be easy to use dataset or datareader?
my purpose of using dataset/datareader is to load the data in a class
thanks.

View 5 Replies View Related

Datareader Problem

Aug 20, 2007

Hi,
I cant seem to get this working right...I have a datareader which i loop through...i want to test each value to see if its null but i cant get the syntax right.
I know i use dr.item("columnname") or dr(0) to pick a certain column but i dont know the column names and want to check them all anyway. What is the syntax to do this.
Thanks for any help...this is prob very simple but just cant see it.
--------------------------------------------While dr.Read
If dr(0) Is System.DBNull.Value Then
Return "test"End If
End While

View 3 Replies View Related

Problem With Datareader

Aug 25, 2007

Hello i creae one programm, there is an two data reader and two Gridview or datagrid , and my programm have one error my programm is there using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class aries : System.Web.UI.Page{ SqlConnection con; SqlCommand cmd; SqlDataReader dr; SqlDataReader dr1; SqlCommand cmd1; protected void Page_Load(object sender, EventArgs e) { string str; str = ConfigurationSettings.AppSettings["DBconnect"]; con = new SqlConnection(str); con.Open(); cmd = new SqlCommand("select color from zodiac_color where Sno=1", con); dr = cmd.ExecuteReader(); GridView1.DataSource = dr; GridView1.DataBind(); cmd1=new SqlCommand("select number from zodiac_number where Sno=1",con); dr1 = cmd.ExecuteReader(); GridView2.DataSource = dr1; GridView2.DataBind(); }} i want to calll two value in a same database but the table is diffrent so please help me ?The error is ::--------- There is already an open DataReader associated with this Command which must be closed first. please help me ashwani kumar

View 2 Replies View Related

Datareader Problem

Apr 9, 2008

hi to all , check this once..this all data related to bus seats SeatNo1,SeatNo2 seats varchar in databaseSt1,St2    status(bit in database sqlserver2000) All my code is working when reader[i + 2].ToString() == "True" is remove from code ..so plz tell me solution gow to ckeck status with datareaderSqlCommand command = new SqlCommand("Select SeatNo1,SeatNo2,St1,St2 from tblSts where
BUSID='S0008'", sqlCon);

        sqlCon.Open();

        SqlDataReader
reader = command.ExecuteReader();

        int x =
0;

        while
(reader.Read())

        {

            for
(int i = 0; i <= reader.FieldCount - 1; i++)

            {

                x = (i + 1);

                System.Web.UI.WebControls.Label myLabel = ((System.Web.UI.WebControls.Label)(Page.FindControl(("Label"
+ x))));

                System.Web.UI.WebControls.CheckBox myCheckbox = ((System.Web.UI.WebControls.CheckBox)(Page.FindControl(("Checkbox"
+ x))));

                if
(reader[i].ToString() != "NULL"
&& reader[i + 2].ToString() == "True"))

                {

                    myLabel.Text =
reader[i].ToString();

                    myCheckbox.Checked = false;

                }

                else

                {

                    myLabel.Text =
reader[i].ToString();

                    myCheckbox.Visible = false;

                }

            }

        }

        sqlCon.Close();

View 8 Replies View Related

Datareader Timeout

Apr 25, 2008

Hi
In my web site I call all the content from the database with the use of querystrings. I use datareader to call the data each time a request appears from the querystring. Although I close all the connections I still get occasionally the following error:
Timeout expired the timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached.

If it is not a programming error then what could it be? I use sql server 2005 and vs 2005 asp.net 2.0 .

View 6 Replies View Related

Open Datareader

May 2, 2008

"There is already an open datareader associated with this command which must be closed first."
I have received this same error before, but I'm not sure why I'm getting it here.'Create a Connection object.
MyConnection = New SqlConnection("...............................")

'Check whether a TMPTABLE_QUERY stored procedure already exists.
MyCommand = New SqlCommand("...", MyConnection)

With MyCommand
'Set the command type that you will run.
.CommandType = CommandType.Text

'Open the connection.
.Connection.Open()

'Run the SQL statement, and then get the returned rows to the DataReader.
MyDataReader = .ExecuteReader()

'Try to create the stored procedure only if it does not exist.
If Not MyDataReader.Read() Then
.CommandText = "create procedure tmptable_query as select * from #temp_table"

MyDataReader.Close()
.ExecuteNonQuery()
Else
MyDataReader.Close()
End If

.Dispose() 'Dispose of the Command object.
MyConnection.Close() 'Close the connection.
End With
As you can see, the connection is opened and closed, and the datareader is closed. Here's what comes next...'Create another Connection object.
ESOConnection = New SqlConnection("...")

If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'"
Else
sqlwhere = " where lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'"
Else
sqlwhere = " where fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'"
End If
End If

dynamic_con = sqlwhere & " order by arr_date desc "

'create the temporary table on esosql.
CreateCommand = New SqlCommand("CREATE TABLE #TEMP_TABLE (".............", ESOConnection)

With CreateCommand
'Set the command type that you will run.
.CommandType = CommandType.Text

'Open the connection to betaserv.
ESOConnection.Open()

'Run the SQL statement.
.ExecuteNonQuery()

End With

'query our side
ESOCommand = New SqlCommand("SELECT * FROM [arrest_index]" & dynamic_con, ESOConnection)

'execute query
ESODataReader = ESOCommand.ExecuteReader()

'loop through recordset and populate temp table
While ESODataReader.Read()

MyInsert = New SqlCommand("INSERT INTO #TEMP_TABLE VALUES("......", ESOConnection)

'Set the command type that you will run.
MyInsert.CommandType = CommandType.Text

'Run the SQL statement.
MyInsert.ExecuteNonQuery()

End While

ESODataReader.Close() 'Create a DataAdapter, and then provide the name of the stored procedure.
MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", ESOConnection)

'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

'Create a new DataSet to hold the records and fill it with the rows returned from stored procedure.
DS = New DataSet()
MyDataAdapter.Fill(DS, "arrindex")

'Assign the recordset to the gridview and bind it.
If DS.Tables(0).Rows.Count > 0 Then
GridView1.DataSource = DS
GridView1.DataBind()
End If

'Dispose of the DataAdapter
MyDataAdapter.Dispose()

'Close server connection
ESOConnection.Close() Again, a separate connection is open and closed.I've read you can only have 1 datareader available per connection. Isn't that what I have here? The error is returned on this line: MyInsert.ExecuteNonQuery()
Help is appreciated.

View 3 Replies View Related

Datareader And Dataset

Nov 3, 2003

Hi

I am using a datareader to access data via a stored procedure. The reason for using the datareader is that the stored procedure is multi level depending on the variable sent to it. However I want to do two things with the data being returned.

The first is to poulate a datagrid - which I've done.
The second is to produce an Infragistic Web Graph. However according to the background reading I have done so far, I can only populate the graph from one of the following: datatable,dataview,dataset,Array or Ilist.

I don't want to make another call to the server for the same information, so how can I get the data out of a stored procedure into a dataset or dataview?

regards

Jim

View 1 Replies View Related

DataReader Not Reading

Jan 13, 2004

Why won't this dataReader read?

Dim objCon2 As New SqlConnection()
objCon2.ConnectionString = "a standard connection string"
objCon2.Open()

Dim objCommand As SqlCommand
objCommand = New SqlCommand(strSQL, objCon2)
Dim objReader As SqlDataReader
objReader = objCommand.ExecuteReader()

Label1.Text = objReader("email")

strSQL is a select command which I've checked (using SQL Query analyzer) does return data. I know the connection string is valid (and I presume if it wasn't that it'd fail on objCon2.open, which it doesn't).

So why oh why do I get this error on the last line (and yes, there is an "email" field in the contents of the reader)

System.InvalidOperationException: Invalid attempt to read when no data is present.

View 1 Replies View Related

Datareader And Arrays

Apr 22, 2004

I have data I am retrieving using a datareader...and SQLSERVER
It could return 1 row of information or perhaps 3 rows of information
I need to know how to use an array here I would guess so I can access each element in this row or rows.

HOw might I use reader.read and get it into the array

View 4 Replies View Related

Clarification On DataReader

Jan 19, 2006

I want to create a DataList that shows products, which will be on
multiple pages. I have my stored proc to show paged results, which
contains a return value for more records.
I have found examples of coding the DataReader, defining all the
parameters etc, but what about the drag and drop SqlDataSource?? You
can select the DataSource Mode to be "DataReader". I can put select
parameters in, with input and my return value. I don't know how to then
access the return value, or output value if needed, from this? My
DataList references the SqlDataSource, but I don't know how to get the
return/output value out??? This is very frustrating, cause I can't find
any info about it anywhere. Always input parameters, but no output.
This is my current SqlDataSource...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
DataSourceMode="DataReader" ConnectionString="<%$
ConnectionStrings:Personal %>" SelectCommand="sp_PagedItems"
SelectCommandType="StoredProcedure">
        <SelectParameters>

<asp:QueryStringParameter Name="Page" QueryStringField="page" />
            <asp:Parameter Name="RecsPerPage" DefaultValue="10" />

<asp:QueryStringParameter Name="CategoryID" QueryStringField="cat"
/>

<asp:Parameter Name="RETURN_VALUE" Direction="ReturnValue" Size="1"
/>
        </SelectParameters>
    </asp:SqlDataSource>

If I take out the RETURN_VALUE Parameter, my results display in my data
list, but that's useless if I can't access the return value to
determine the remaining number of pages etc. Is my RETURN_VALUE
parameter wrong? How do I access that? My stored proc is shown below...

CREATE PROCEDURE sp_PagedItems
   (
    @Page int,
    @RecsPerPage int,
    @CategoryID int
   )
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
   ID int IDENTITY,
   No varchar(100),
   Name varchar(100),
   SDescription varchar(500),
   Size varchar(10),
   ImageURL varchar(100)
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (No, Name, SDescription, Size, ImageURL)
SELECT No, Name, SDescription, Size, ImageURL FROM Products WHERE CategoryID=@CategoryID

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords =
   (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.ID >= @LastRec
   )
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

View 3 Replies View Related

RecordCount With Datareader

Apr 22, 2006

I am very disappinted where Datareader have no RecordCount where I can get the total records it read. I guess I found a way:

sql = "SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS c FROM ACCOUNTS ORDER BY c DESC"Dim command As SqlCommand = New SqlCommand(sql, New SqlConnection(_DBConnectionString)) command.Connection.Open() _ReturnDataReader = command.ExecuteReader(CommandBehavior.CloseConnection) command.Dispose() _TotalRecord = _ReturnDataReader.GetInt64(_ReturnDataReader.GetOrdinal("c"))
Have SQL Server 2005 count for me....

View 1 Replies View Related

Datareader Security

Jun 15, 2007

As a beginner, I'm attempting to set up security for SQL Server But it's not working and am fairly confused at the moment.

The current problem is to do with getting SQL Security logins to work. I've added a login for a 'reader' user (no updates allowed) and have set the database user to use only the 'db_datareader' role. Yet, when I logon as that user, I can update data without a problem.

The system is a standalone PC using XP Pro with SQL Express installed.

The connection string is valid (and works, allowing access to all data) and is of the type:
"server=xxxxx;database=xxxx;Trusted_Connection=True;"

The application is written is VB.NET

I hope I'm making some simple mistake here and I'd really appreciate any thoughts ...

Many thanks

View 6 Replies View Related

DataReader.GetFloat From SQL DB

Apr 22, 2008

Hi All,
I am the following problem retrieving Float data from a Compact SQL database, it worked perfectly with 1.1 .Net CF, but since updating to 2.0 .Net CF its crashing with a "InvalidCastException" Error, have checked the DB its a Float...

// Start of code snip

cmd = conn.CreateCommand();

string cmdString ="select " +

RECORDNUMBER_COLUMN + ", "

+ FULLNAME_COLUMN + ", "

+ FPFILENAME_COLUMN + ", "

+ IMAGENAME_COLUMN + ", "

+ BMPNAME_COLUMN + ", "

+ TEMPLATENAME_COLUMN + ", "

+ SCORE_SCORE_COLUMN + " "

+ "from " + ENROLTABLE_NAME + " where " + RECORDNUMBER_COLUMN + " = " + iNumber;

cmd.CommandText = cmdString;

SqlCeDataReader DataReader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);

while(DataReader.Read())

{

recordnumber = DataReader.GetInt32(DS_WORD_COLUMNINDEX_RECORDNUMBER);

fullname = DataReader.GetString(DS_WORD_COLUMNINDEX_FULLNAME);

fpdataname = DataReader.GetString(DS_WORD_COLUMNINDEX_FPFILENAME);

fpimagename = DataReader.GetString(DS_WORD_COLUMNINDEX_IMAGENAME);

personbmpname = DataReader.GetString(DS_WORD_COLUMNINDEX_BMPNAME);

persontemplatename = DataReader.GetString(DS_WORD_COLUMNINDEX_TEMPLATENAME);
//*******************************************************

Score_Score = DataReader.GetFloat(DS_WORD_SCORE_COLUMNINDEX_TEMPLATESCORE);
// This generates the error : "InvalidCastException"
//*******************************************************

ReturnValue=true;

}

DataReader.Close();

conn.Dispose();

cmd.Dispose();

// End Of Snip

Cheers for any help with this

Adz

View 1 Replies View Related

SQL Command Datareader

Jul 31, 2007

I have the following SQL statement in the DataReader.SQLCommand.

select * from myTable where DataEdited > 'some hard coded date data'

I want to read the above 'some hard coded date data' from a table sitting in different database which is not the same server where the DataReader is connected.

How to do that?

I can populate the 'some hard coded date data' to a SSIS variable but do not know how to embed that in the SQL statement.

Thank you for your thoughts.

Smith

View 9 Replies View Related

DataReader And Bad Dates

Aug 9, 2006

Hello,

I am using a DataReader to read an ODBC Data Source.

Some of my dates are not valid. I would like to have the DataReader just treat the bad dates as Null. However I cannot seem to find a setting that will get me past the DataReader.

In SQL 2000 DTS I was able to use a script to skip the bad dates.

Please help because I don't want to have to run SQL 2000 DTS on SQL 2005.

Thanks,

Michael

View 2 Replies View Related

Datareader Does Not Return Result

Feb 23, 2007

the following is the code,....When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...
Connection is open and the query is simple select statement

SqlCommand myCommand = new SqlCommand(query,myConnection);
myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

Thanks
Niranch

View 7 Replies View Related

About Datareader And Dataset Combine Use

Dec 26, 2007

i have 2 database queries. One fetches data from a table and other according to first query result second query fetches record from another table both of these queries make use of datareader and while loop for inserting data to dropdownlist , but instead of that i want to add all my row fetched by second query store one by one in a dataset for some time .in simple words i want to insert datareader record one by one in dataset. i am using asp.net2.0,c#, Sql server 2000

View 2 Replies View Related

How To Add Datareader Results To A Label

Feb 21, 2008

hi,
i have this code it gets the records from db
i need to add the results of the datareader to a label but label control does not have a datasource property
second problem is how to comma separate the results.with array? or what please help
i need to show the results like this: record1,record2,record3Dim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
Dim strSQL As StringDim dr As SqlDataReader
Dim userID As Integer = Session("cmmLogonUserID")
strSQL = "select id,extension from musicExt"Dim cmd As New SqlCommand()cmd = New SqlCommand(strSQL, Conn)
Conn.Open()
dr = cmd.ExecuteReader()

'txtMediaFormats.Text = dr ???????
' ddlGenres.DataBind() ????????
Conn.Close()

View 6 Replies View Related

Open DataReader Error

Apr 16, 2008

Looking at the below code you can see that I have a separate Connection for each Insert Statement.'OPEN CONNECTION TO ESOSQL
MyConnection = New SqlConnection("......")
MyConnection.Open()

'check if there are existing charges for this person. The user must enter in atleast 1 charge before proceeding with arrest insert.
MyCheck = New SqlCommand("SELECT * FROM ARREST_CHARGES WHERE ARRESTNO = '" & Session("uid") & "'", MyConnection)
MyCheck.CommandType = CommandType.Text
MyDataReader = MyCheck.ExecuteReader

If MyDataReader.HasRows Then

MyConnection1 = New SqlConnection("...")
MyConnection1.Open()

MyInsert = New SqlCommand("INSERT INTO ARREST_INDEX (ARRESTNO, NOTES) VALUES ('" & Session("uid") & "','" & tx_notes.Text & "')", MyConnection1)

MyInsert.CommandType = CommandType.Text
MyInsert.ExecuteNonQuery()

MyConnection1.Close()

MyConnection2 = New SqlConnection("....")
MyConnection2.Open()

MyOtherInsert = New SqlCommand("INSERT INTO ARREST_COMMENTS (ARRESTNO, NOTES) VALUES ('" & Session("uid") & "','" & tx_notes.Text & "')", MyConnection2)

MyOtherInsert.CommandType = CommandType.Text
MyOtherInsert.ExecuteNonQuery()

MyConnection2.Close()

Label1.Text = ""
Else
div1.Style.Add("display", "block")
Label1.Text = "You must enter charges before proceeding."
End If
MyConnection.Close()One would think I should only have to use one connection. However, if I use only one I get this error:There is already an open DataReader associated with this Command which must be closed first. 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.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.Source Error: Line 60:
Line 61: MyInsert.CommandType = CommandType.Text
Line 62: MyInsert.ExecuteNonQuery()
Line 63:
Line 64: ' MyConnection1.Close()The only way I could get rid of it was to encapsulate each individual INSERT statement within its own connection. This seems to me as very inefficient. Can anyone explain why I couldn't just use one connection?Thanks.

View 5 Replies View Related