Invalid attempt to read when no data is present.

Question

dwinn 0 Junior Poster in Training

11 Years Ago

Hi everyone,

In a nutshell...I have developed a site in ASP.Net and VB.Net which allows a user to create an order and submit it to a SQL Server backend database.

When the order is placed...an xml file is also produced, showing the items that have been ordered...which is then loaded in to a program to update stock quantities.

The code I have is as follows:

Public Sub butSubmitOrder_Click(sender As Object, e As EventArgs) Handles butSubmitOrder.Click

                Dim sqlConn As New SqlConnection
                Dim sqlCmd As New SqlClient.SqlCommand
                Dim sqlReader As SqlDataReader
                'Dim sqlAdapter As New SqlDataAdapter
                Dim sqlDataTable As New DataTable
                Dim ds As New DataSet
                Dim objOutlook As Object
                Dim objOutlookMsg As New MailMessage()
                Dim mailTo As String
                Dim body As New StringBuilder
                Dim xmlFile As String

                           '------------------------------------------------------Generate CSV file with order details-------------------------------------------------

                'Dim csvFile = "C:\Order" & lblOrderRef.Text & ".xml"
                xmlFile = "C:\PWC\PrismOrder" & lblOrderRef.Text & ".xml"

                sqlConn.ConnectionString = "server=servername;Initial Catalog=dbname;Persist Security Info=True;User ID=loginid;Password=pword;Trusted_Connection=yes"

                'Here we are opening the connection to the database.
                sqlConn.Open()

                'This is to say that sqlCmd is a stored procedure.
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure

                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                sqlCmd = sqlConn.CreateCommand

                sqlCmd.CommandText = "SELECT DISTINCT DateRequired, o.OrderReference, DeliveryAddressLine1, DeliveryAddressLine2, DeliveryAddressLine3, PostTown, Postcode, StockCode, Quantity FROM tblOrders o INNER JOIN tblAddress a ON o.OrderReference = a.OrderReference AND o.OrderReference = '" & lblOrderRef.Text & "'"

                sqlReader = sqlCmd.ExecuteReader

                Dim objWriter As StreamWriter = IO.File.AppendText(xmlFile)

                If IO.File.Exists(xmlFile) Then

                    If sqlReader.HasRows = True Then

                        objWriter.WriteLine("<?xml version='1.0'?>")
                        objWriter.WriteLine("<PWCScript Version='2010.9.3'>")
                        objWriter.WriteLine("   <Table Action='Add' Name='SH'>")
                        objWriter.WriteLine("       <Update Name='SH_CUST' Value='YO01'/>")
                        objWriter.WriteLine("       <Update Name='SH_REQ_DEL_DATE' Value= '" & sqlReader.GetString(0) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_CUST_REF' Value= '" & sqlReader.GetValue(1) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_ADDRESS' Value= '" & sqlReader.GetValue(2) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_SUBURB' Value= '" & sqlReader.GetValue(3) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_CITY' Value= '" & sqlReader.GetValue(4) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_STATE' Value= '" & sqlReader.GetValue(5) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_POST_CODE' Value= '" & sqlReader.GetValue(6) & "'/>")

                            Do Until sqlReader.Read = False

                                objWriter.WriteLine("   <Table Action='ADD' Name='SD'>")
                            objWriter.WriteLine("       <Update Name='SD_STOCK' Value= '" & sqlReader.GetValue(7) & "'/>")
                            objWriter.WriteLine("       <Update Name='SD_QTY_ORDER' Value= '" & sqlReader.GetValue(8) & "'/>")
                                objWriter.WriteLine("   </Table>")

                            Loop
                            objWriter.WriteLine("   </Table>")
                            objWriter.WriteLine("</PWCScript>")

                            objWriter.Close()
                            sqlConn.Close()
                    Else
                            MsgBox("There is no data available for the program")
                    End If
                End If

                Response.Redirect("OrderConfirmation.aspx")

            End Sub

When I run the program and get to this part...I get the following message:

'Invalid attempt to read when no data is present.'

Pointing at the following line:

objWriter.WriteLine(" <Update Name='SH_REQ_DEL_DATE' Value= '" & sqlReader.GetString(0) & "'/>")

If i put this paragraph inside the while loop, it prints fine, but doesnt go in to my program because of being an incorrect format.

Can somebody please tell me what it is I am doing wrong?

Many thanks,

Dan

sql vb.net

3 Contributors
5 Replies
2K Views
19 Hours Discussion Span
Latest Post 11 Years Ago Latest Post by dwinn

All 5 Replies

Begginnerdev 256 Junior Poster

11 Years Ago

You can try placing an IIF into the lines to test against empty or null values.

Example:

    objWriter.WriteLine(" <Update Name='SH_REQ_DEL_DATE' Value= '" & IIf(sqlReader.GetString(0), IsNothing(sqlReader.GetString(0)) = False, IsNothing(sqlReader.GetString(0)) = True) & "'/>")

poojavb 29 Junior Poster

11 Years Ago

u need a read method...the reader is not able to read the data....

u r not using the read method and u are trying to get the values....that is why u are getting this error

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

dwinn 0 Junior Poster in Training · Answer 1 · 2012-06-20T06:57:32+00:00

I have tried putting your line above in to my code and I am still getting the same error pointing at that line.

If I put it all inside the Do Until though, it all works.

I know that the sqlDataReader has rows, but for some reason, it can't be read until the Do Until.

Any more ideas please?

Regards,

Dan

dwinn 0 Junior Poster in Training · Answer 2 · 2012-06-20T07:37:49+00:00

Hi poojavb,

Thank you for your post.

Could I do this using:

sqlReader.Read = True

And put this where I currently have:

If sqlReader.HasRows = True Then

Or do I need to do it a different way?

Thanks,

Dan

dwinn 0 Junior Poster in Training · Answer 3 · 2012-06-20T10:12:03+00:00

I have managed to fix this now...for anyone who is interested...I produced one sqlReader for the SH section and then another sqlReader for the SD section and looped through both sets as can see below:

xmlFile = "C:\Order" & lblOrderRef.Text & ".xml"

                sqlConn.ConnectionString = "server=server;initial Catalog=dbname;Persist Security Info=True;User ID=userid;Password=pword;Trusted_Connection=yes"

                'Here we are opening the connection to the database.
                sqlConn.Open()

                'This is to say that sqlCmd is a stored procedure.
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
                sqlCmd1.CommandType = System.Data.CommandType.StoredProcedure

                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                sqlCmd1 = sqlConn.CreateCommand
                sqlCmd = sqlConn.CreateCommand

                sqlCmd.CommandText = "SELECT DISTINCT DateRequired, o.OrderReference, DeliveryAddressLine1, DeliveryAddressLine2, DeliveryAddressLine3, PostTown, Postcode FROM tblOrders o INNER JOIN tblAddress a ON o.OrderReference = a.OrderReference AND o.OrderReference = '" & lblOrderRef.Text & "'"
                sqlCmd1.CommandText = "SELECT DISTINCT StockCode, Quantity FROM tblOrders o INNER JOIN tblAddress a ON o.OrderReference = a.OrderReference AND o.OrderReference = '" & lblOrderRef.Text & "'"

                sqlReader = sqlCmd.ExecuteReader

                Dim objWriter As StreamWriter = IO.File.AppendText(xmlFile)

                If IO.File.Exists(xmlFile) Then

                    Do Until sqlReader.Read = False

                        objWriter.WriteLine("<?xml version='1.0'?>")
                        objWriter.WriteLine("<PWCScript Version='2010.9.3'>")
                        objWriter.WriteLine("   <Table Action='ADD' Name='SH'>")
                        objWriter.WriteLine("       <Update Name='SH_CUST' Value='YO01'/>")
                        objWriter.WriteLine("       <Update Name='SH_REQ_DEL_DATE' Value= '" & sqlReader.GetValue(0) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_CUST_REF' Value= '" & sqlReader.GetValue(1) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_ADDRESS' Value= '" & sqlReader.GetValue(2) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_SUBURB' Value= '" & sqlReader.GetValue(3) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_CITY' Value= '" & sqlReader.GetValue(4) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_STATE' Value= '" & sqlReader.GetValue(5) & "'/>")
                        objWriter.WriteLine("       <Update Name='SH_POST_CODE' Value= '" & sqlReader.GetValue(6) & "'/>")

                    Loop

                    sqlReader.Close()
                    sqlReader1 = sqlCmd1.ExecuteReader

                    Do Until sqlReader1.Read = False

                        objWriter.WriteLine("   <Table Action='ADD' Name='SD'>")
                        objWriter.WriteLine("       <Update Name='SD_STOCK' Value= '" & sqlReader1.GetValue(0) & "'/>")
                        objWriter.WriteLine("       <Update Name='SD_QTY_ORDER' Value= '" & sqlReader1.GetValue(1) & "'/>")
                        objWriter.WriteLine("   </Table>")

                    Loop
                    objWriter.WriteLine("   </Table>")
                    objWriter.WriteLine("</PWCScript>")

                    objWriter.Close()
                    sqlConn.Close()
                Else
                    MsgBox("There is no data available for the program")
                End If

Thanks,

Dan

Invalid attempt to read when no data is present.

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers