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
