Can i use SQLDataReader instead of a Recordset. I want to achieve the following result in an SQLDataReader.
Dim dbConn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlstr As String = "SELECT Name,Status FROM table1 WHERE id=" +
[Code].....
can i replace recordset with SQLDataReader and if I can can you please show me the changes in code.
How do you change an ADODB recordset to ADO.Net recordset?
View 4 RepliesDoes anyone know a Good tutorial on how to use The SqlDataReader in VB.Net ?
View 4 RepliesI'm not sure about this code. What I wanted to do is to count the number of records in the table that I selected and then add a value of 1 to it to generate a transaction number.
I'm not sure on how to count the number of rows in the reader. So far, this is my code.
Dim intTransaction As Integer = 0
Try
con.Open()
[Code]....
How can I reuse(reopen) same Datareader after calling Close() ??
View 1 RepliesMy SQL query shows 8 rows when executed but the following code only shows 7. Apparently, it�s missing the first row.
...
Dim temp as String = Nothing
Dim r As SqlDataReader = objCmd.ExecuteReader()
If r.Read() = True Then
[Code]......
I would like to pass two values to a stored procedure. I am using a sqlreader to export data to an excel sheet. I need the user to be able to select a range, rather that the whole sql table. the code works fine without adding parameters, but I need the user to be able to select a range, rather that the whole table.
Dim data As String = ""
sqlCon.Open()
sqlCmd = New SqlCommand(strSQL, sqlCon)
[code]....
I am returning one row from the database, and I want to convert the SQLDataReader to a string format, so I can pass it to my webservice.
Dim rdr As SqlDataReader = sqlcmd.ExecuteReader
If rdr.HasRows Then
rdr.Read()
[Code].....
I'm building a custom calendar control that queries our database to display corporate events. Here is the situation, there is an EndDate value stored and on the dev system one of the events have a NULL value. No big deal since it's only a test system, but might as well check before trying to use it anyway on the safe side. I figured the following code would work:
While dr.Read()
corporateTable.Rows.Add(New Object() { _
Convert.ToDateTime(dr("EventBeginDate")) _
[code]....
The thing that really gets me is, at one point I had this:
, IIf(dr.IsDBNull(column), Convert.ToDateTime(dr("EventBeginDate")).AddDays(1), Convert.ToDateTime(dr("EventEndDate"))) _
Thinking that it should work because it should only evaluate the dr() if it's not NULL. However, it kept erroring out at the end because the value was in fact NULL.Why is it that even though I'm checking if it's NULL before using the value, it errors out at the part that doesn't get called unless it's not NULL? Does it have to do with the fact that I'm using the IIF() and it's evaluating the whole statement? Or, using the dr(), it evaluates at runtime?
I am writing a simple XML web service and want to return raw XML. I am doing this by creating a string literal that represents the XML. My data source is a SqlDataReader object that contains the results of a stored proc call. The way I am doing it right now is by using a while loop and reading values from the item property like so :
[Code]...
I just want to verify something. I believe it is likely that if I apply the using command to a SqlDataReader, that it will both close the data reader and dispose of it. For example:
Using sdr As SqlDataReader = cm.ExecuteReader()
Dim someInt As Integer = sdr.GetInt32(0)
'other details and actions
End Using
Will that close the sdr SqlDataReader after it exits the Using code block. (I believe it will, but just want to verify.)
What would happen if you call Close() on a SqlConnection object before you call Close() on a SqlDataReader using that connection?
Actually, what I really want to know is whether or not the order in which you Close them matters. Does calling SqlConnection.Close() completely close the connection, or will it remain open if you do not call Close() on a SqlDataReader using that connection?
I don't think I really understand how connection closing works.
How do i get the number of columns in a sqlDataReader after executing sql statement ?
View 4 RepliesI`m bussy building a ORM for our applications.I don`t want to use all of microsoft data functions like ADO.NET and Datasets because i would like to understande the base and manipulate data by code. I also want to make it later work like building a database out of the code in stead of the other way around.
I have build a simple appliacation inside VB.NET with a dataset and ADO.NET Entity.. When loading 250.000 records into objects it takes about 4.5 second. I`m using datareader to load a record of a database into a object dynamicly. I started at 15 seconds,later 8 and nu 6.5 seconds for the total amount of records.I started with TmpObject.ColumnName = Reader ("ColumnName"), later i figured out Reader.GetValues(TmpObjectArray) in combination with TmpObject.ColumnName = TmpObjectArray(0) is faster. Now i tought if i can set the properties as pointer to a object array so the values are placed onto the object instead of on a different object before it could be faster.
' PropertyArray
Dim tPropertyArray()
As PropertyInfo = tProperties.Values.ToArray[code]......
I am a beginner in VB.Net. I have a table tblName; which consist of columns 'Name' and 'ID'. I want to populate the comboBox, where displaymember = 'Name' and ValueMember = 'ID'. In short, when a user selects a 'Name', return value should be the 'ID'. I can do it using DataAdapter. Another way of doing is; using a Listbox in parralel with the comboBox, which will be hidden, to save the 'ID' .
[Code]...
I need to populate all the values of a specific column in each rows of my table from my database. The specific column that i need is the "position",I'm using sqldatareader here's my code. I know that I need to use looping statements and the items.add properties of the combobox but I dont know how will I do it..hope you help me here..
Private Sub cboPosition_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboPosition.Click
scmd = New SqlCommand("SELECT KPIPOSTCODE FROM KKPIPOSITION", sqlcon)
sqlcon.Open()
scmd.CommandType = CommandType.Text
[code].....
I am currently using a sql data reader (in vb.net) to extract an article object via a stored proc from a SQL Server 2008 database. Part of this object includes the two properties shown below:
theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness"))))
theArticle.Relevance = ((myReader.GetInt32(myReader.GetOrdinal("Relevance"))))
My problem is that the Truthfulness and Relevance may return a null value and this is causing the function to fall over.I think I understand why. I am asking for an integer value (getin32) and because null is returned it fails.How do I accommodate the null value from the database so it does not fall over?
My question is: Using SqlDataReader. Suppose I have SQLdr instantiated. I can get data from some column using:
SQLdr("some_column")
Now if I know that column "Value" has a some value, I can get it this way:
eg. 1
[Code]...
My motivation is to have easy-on-the-eyes code (not too dense on the page with long expressions). I have one application I'm studying where there are hundreds of expressions like eg. 1. If I could get the ordinal values at the start, I could use expressions like eg. 2 instead. As a bonus, I would eliminate redundant calls to GetOrdinal for queries that return thousands of rows.
however, this is clearly erroneous if the ordinal values might change between calls to SQLdr.Read().
This is incredibly urgent, I need to present this application in 3 and a half hours.My application checks against a data source to see if a value exists in the database and changes values depending on whether or not the value in question was found.The problem is that I've run the sql query with the value in question in SSMS and no rows were returned, and yet, my DataReader says it has rows.[code]val will only be returned True if the NumberToCheck (in this example 3235553469) exists in the database.Having copied the value of NumberToCheck into SSMS and testing the query there, I can verify that the query does work as expected.No, I can't populate a DataSet because of the volume of information in the table (+/- 9.5m rows). Even with the 'WHERE' filter, the query is too heavy on resources and eventually ends in an OutOfMemory Exception which is why I went with a DataReader.
View 4 RepliesMy code where errors occurred:
strSQL = "select * from transactions where TransactionID='" + txtTransactionID.Text + "'"
ReaderQuery(strSQL)
While dr.Read
[code].....
The weird thing about this, is that when I first run it, I was able to Bind it to all the Textbox and thought everything went smoothly, but when I run it again without any changes in that code, that error suddenly appears... sometimes that error went to other dr.item(""
Some tables I am dealing with have null values and are throwing errors. So far ive tried a few solutions to deal with the nulls with no success.Here are the code samples from my efforts so far;
If (r("datemodified").Equals(DBNull.Value)) Then
datemodified = String.Empty
Else[code]....
i have ran the query and it is working ok (i.e all the cols exist)
Scenario is this; I've a sqldatareader query that seems to be returning nothing when I try to convert the sqldatareader to a datatable using datatable.load.So I debug into it, I grab the verbose SQL query before it goes into the sqldatareader, just to make sure it's formatted correctly. I copy and paste this into SQL server to run it and see if it returns anything. It does, one row.I go back to visual studio and let the program continue, I create a datatable and try to load the sqldatareader but it just returns an empty reader. I'm baffled as to what's going on. [code] Ok I've done a loop to count the rows of the datareader before using the datatable.load method. In my test query I'm getting 4 rows (the correct amount) in the datareader, as soon as I use the datatable.load method it says the datatable is empty, what's going on?! So it seems a datareader is a one-way thing, iterate through it to get the row count and it's essentially empty as far as any other bit of code is concerned. Didn't realise it was forward only and wouldn't 'reset' once read through.
View 1 RepliesDim myreader As SqlDataReader = cmd.ExecuteReader
'read information from the database and give the values to the arguments(column_1, and column_2)
While myreader.Read
column_1 = myreader.GetString(1)
column_2 = myreader.GetString(2)
.....
I get an error when the data returned from myreader.GetString(2) is NULL. How do I check for nulls before assigning column_2 a value?
I am running queries using sqlcommand and am trying to get the results from sqldatareader into a dataset table. It works fine when loading the datareader data as a row into the dataset table and writes it to a crystal report.I would like to have depending on the query results have the data in the appropriate columns and it only happens if the results are from all fields.
Dim CommandObj2 As New SqlCommand(sql, cnn)
sqldr = CommandObj2.ExecuteReader()
While sqldr.Read()
Dim dataRow As DataRow = ds.Tables("NTable").NewRow()
[code]....
MsgBox(ds.NTable.Rows.Count, MsgBoxStyle.OkOnly, "Records Count")My dataset and table have the same name, NTable. Question again is how to populate the dataset columns accordingly.
Some tables I am dealing with have null values and are throwing errors. So far ive tried a few solutions to deal with the nulls with no success.
Here are the code samples from my efforts so far;
If (r("datemodified").Equals(DBNull.Value)) Then
datemodified = String.Empty
[code].....
Here's a simple code snippet of a larger function as an example.
Using conn as New SqlConnection("conn string")
Using find as new SqlCommand("ExampleProc",conn)
Dim rParam as new SqlParameter("@RESULT",SqlDbType.Int)
[code]....
I know the Output parameter is returned after the SqlDataReader is closed. From what I think I know, the Using statement will call .Dispose on the SqlDataReader, so will that basically wipe out the Output parameter? If so, what is the best method to call a SqlDataReader that also contains Output parameters that closes and disposes everything correctly? Let me add that based on what I've read you only have access to the Output parameter using a SqlDataReader after you call .Close on the SqlDataReader.
I have a SqlDataReader and need to read a varbinary(max) column from it using the SqlDataReader.GetBytes() method. This method populates a byte array and therefore needs to know what length of data to read.This is where I get confused.. Clearly I want to read all the data that has been returned from the database in this row/column so what 'length' parameter should I pass?As far as I can see, the SqlDataReader doesn't provide any methods to discover what length of data is available, therefore this method seems fairly awkward to me.I'm tempted to just pass int.MaxValue here and forget about the issue but something about this doesn't sit right with me.
I am aware that I can instead call
byte[] value = (byte[])dataReader["columnName"];
.. and this seems to completely take care of the length issue internally. However I am working with a set of complicated code generation templates that have been built around the SqlDataReader.GetXXXX() methods. So I am tied into using GetBytes and need to understand its proper usage.
I have a SqlDataReader and need to read a varbinary(max) column from it using the SqlDataReader.GetBytes() method. This method populates a byte array and therefore needs to know what length of data to read.This is where I get confused.. Clearly I want to read all the data that has been returned from the database in this row/column so what 'length' parameter should I pass?As far as I can see, the SqlDataReader doesn't provide any methods to discover what length of data is available, therefore this method seems fairly awkward to me.I'm tempted to just pass int.MaxValue here and forget about the issue but something about this doesn't sit right with me.
View 2 RepliesI have sqldatareader that picks up x number of rows, I would like all of them to appear in one textbox. Is that possible and how can I accomplish this?[code]
View 11 RepliesI am using a sqldatareader to read some values into a form from my database. I would like to know the number of rows returned by my query and I cant figure out how to do this. I want to use the number of rows returned so that I can redimension an array of values to the correct size. Here is my code I am working with:
[Code]...