ExecutionReader error

sirrahhc

New member
Joined
Jan 3, 2006
Messages
4
Location
Nashville
Programming Experience
1-3
Hey guys, what's up?? I am creating a console app that will take input from a user, A customer's SSN and then run some SQL code against that SSN and update some fields in the backend (SQL DB). This will then create a field called MatterID (MID) in the backend as well. All of this works but I essentially want to return the newly created MID from SQL and dump it into a text box on the Form so the user know's the created MID without looking elsewhere. I am getting the "ExecutionReader: CommandText property has not been initialized" error. I know I am not including the ExecutionReader in my code but it's because I'm ignorant to how to use this function. I've search the web for several days and can't really find anything that I can relate to my project. I am including my code and welcome any and all suggestions.

All of this code down to the End Sub works fine....

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click

Public PCCONNECTIONSTRING As String
PCCONNECTIONSTRING = "packet size=4096;user id=" & Trim(Me.txtLogin.Text) & ";data source=" & My.Settings.DataSource_2 & ";persist security info=True;initial catalog=" & My.Settings.InitialCatalog_2 & ";password=" & Trim(Me.txtPassword.Text)


'This ensures that the user enters a valid social security number
If Trim(Me.txtSSN.Text) = "" Then
MsgBox("The Social Security Number you are trying to Force Add does not exist", MsgBoxStyle.OkOnly, "SSN Not Found")
Exit Sub
End If

'This ensures that the user enters a user name and login
If Trim(Me.txtPassword.Text) = "" Or Trim(Me.txtLogin.Text) = "" Then
MsgBox("A login and password are required")
Exit Sub
End If

Dim YResult As Integer
YResult = MsgBox("Are you sure you want to force this record?", MsgBoxStyle.OkCancel)

If YResult = vbOK Then
'this was ExistingMatter
Dim SSNSearch As String = ""
Trim(ValFromTable("INSERT INTO Siebel_DataFeed_MCC (LAST_UPD, LAST_UPD_BY, CREATED " & _
",CREATED_BY, MODIFICATION_NUM, ROW_ID, X_MEM_ADDR, X_MEM_BIRTH_DT " & _
",X_MEM_CITY, X_MEM_FST_NAME, X_MEM_LAST_NAME, X_MEM_SSN " & _
",X_MEM_STATE, X_MEM_ZIPCODE, X_COUNTY, X_REGION_CODE " & _
",X_RELATION_TYPE_CD, ALT_CONYACT_NAME, ALT_CON_PH_NO " & _
",SR_AREA, ASGN_USR_EXCLD_FLG, DESC_TEXT, OWNER_EMP_ID, LOGIN " & _
",BU_ID, PR_SYMPTOM_CD, SR_PRIO_CD, ROW_STATUS, SR_NUM, SR_TYPE_CD " & _
",SR_STAT_ID, SR_SUB_STAT_ID, CUST_CON_PH_NUM, X_CURRENT_MCO " & _
",X_REQUESTED_MCO, DateInserted, ProcessFlag, ErrorCodes, matters) " & _
"SELECT LAST_UPD, LAST_UPD_BY, CREATED, CREATED_BY, MODIFICATION_NUM " & _
",ROW_ID, X_MEM_ADDR, X_MEM_BIRTH_DT, X_MEM_CITY, X_MEM_FST_NAME " & _
",X_MEM_LAST_NAME, X_MEM_SSN, X_MEM_STATE, X_MEM_ZIPCODE, X_COUNTY " & _
",X_REGION_CODE, X_RELATION_TYPE_CD, ALT_CONYACT_NAME, ALT_CON_PH_NO " & _
",SR_AREA, ASGN_USR_EXCLD_FLG, DESC_TEXT, OWNER_EMP_ID, LOGIN " & _
",BU_ID, PR_SYMPTOM_CD, SR_PRIO_CD, ROW_STATUS, SR_NUM, SR_TYPE_CD " & _
",SR_STAT_ID, SR_SUB_STAT_ID, CUST_CON_PH_NUM, X_CURRENT_MCO " & _
",X_REQUESTED_MCO, DateInserted, 'N', NULL, NULL " & _
"FROM Siebel_ErrorHistory_MCC " & _
"where x_mem_ssn='" & Me.txtSSN.Text & "'", SSNSearch))

Dim DeleteMID As String = ""
Trim(ValFromTable("Delete FROM Siebel_ErrorHistory_MCC where x_mem_ssn = '" & Me.txtSSN.Text & "'", DeleteMID))

Dim ExecSP As String = ""
Trim(ValFromTable("EXEC Siebel_ProcessMCCDataFeed", ExecSP))

Dim MIDSearch As String = ""
txtMID.Text = Trim(ValFromTable("SELECT MatterID FROM Siebel_InsertHistory_MCC " & _
"where x_mem_ssn='" & Me.txtSSN.Text & "'", MIDSearch))

If txtMID.Text = "" Then
MsgBox("The system was unable to Force the requested record. Please check the existing Social Security Number and try again.")
Else
MsgBox("The requested record has been forced.")
End If

'Return Focus to the form if the user selects NO from the MsgBox
Else
Me.Focus()
End If

End Sub

---------------------------------------------
This bit below is where it is falling over....I'm trying to append this into the above function but get the error when I do...

Dim strField As String = ""
Dim strSQL As String
strSQL = Trim(ValFromTable("SELECT Row_ID FROM Siebel_InsertHistory_MCC " & _
"where x_mem_ssn='" & Me.txtSSN.Text & "'", strField))

'this is where im trying to assign the value to the text box
Me.txtDisplay.Text = ValFromTable(strSQL, strField)



...any suggestions?? Thanks in advance guys!!!!!
 
Solution

Hey guys, I was able to work through this with the code I created below. I love it when you can resolve your own issues! It was the ExecuteScalar() function that I was looking for. The problem I was having is that I could not get ExecuteReader() to work. So all is well now, thanks for looking!

Dim strConnection As String = "packet size=4096;user id=" & Trim(Me.txtLogin.Text) & ";data source=" & My.Settings.DataSource_2 & ";persist security info=True;initial catalog=" & My.Settings.InitialCatalog_2 & ";password=" & Trim(Me.txtPassword.Text)
Dim objConnection As New SqlClient.SqlConnection(strConnection)
Dim strResult As String
Dim strSQL As String = "SELECT MatterID FROM Siebel_InsertHistory_MCC " & _
"where x_mem_ssn='" & Me.txtSSN.Text & "'"
Dim objCommand As New SqlClient.SqlCommand(strSQL, objConnection)
objCommand.Connection.Open()
strResult = objCommand.ExecuteScalar().ToString
objConnection.Close()
txtMID.Text = strResult
 
Any time that you execute a query that you want to return results from, use ExecuteReader() - it returns a reader from which you can pull many items of data
Any time you wish to return a single value, use ExecuteScalar() - executescalare essentially carries out any query and then only returns you the top-leftmost value you would see in a grid of results
Any time that you execute a query not intended to return data, but to carry out data manipulation (insert, update, delete), use ExecuteNonQuery()
 
Back
Top