User-Defined Class not Responding!!!

  • Thread starter Thread starter Luis E. Hernandez
  • Start date Start date
L

Luis E. Hernandez

Hello,

I am running a MDI project with several forms. One form collects information
from the user via combo_boxes and text boxes.

I am attempting to process that information via a class I called
"clsCustomer.vb"

My code calls a function in the clsCustomer class entitled "save_customer".
The code is listed below
-=-=-=-=-=-=-=-=-=-
Public Sub save_customer()

Dim strSQL As String = "SELECT * from customer"

Dim MyCommand As New SqlCommand()
Dim rdrDataReader As SqlDataReader
Dim strLastModifiedDate As DateTime
Dim intCounter As Long

strLastModifiedDate = Now()

'if the customer is a new customer do an insert, otherwise do an
update
If Me.customer_id > 0 Then
strSQL = ""
strSQL = strSQL & "UPDATE customer "
strSQL = strSQL & "SET "
strSQL = strSQL & "customer_type_id = " & Me.customer_type_id &
", "
strSQL = strSQL & "customer_status_id = " &
Me.customer_status_id & ", "
strSQL = strSQL & "surname = '" & Me.surname & "', "
strSQL = strSQL & "first_name = '" & Me.first_name & "', "
strSQL = strSQL & "last_name = '" & Me.last_name & "', "
strSQL = strSQL & "middle_initial = '" & Me.middle_initial & "',
"
strSQL = strSQL & "suffix = '" & Me.suffix & "', "
strSQL = strSQL & "address_1 = '" & Me.address_1 & "', "
strSQL = strSQL & "address_2 = '" & Me.address_2 & "', "
strSQL = strSQL & "address_2_prefix = '" & Me.address_2_prefix &
"', "
strSQL = strSQL & "city = '" & Me.city & "', "
strSQL = strSQL & "state = '" & Me.state & "', "
strSQL = strSQL & "zipcode = '" & Me.zipcode & "', "

strSQL = strSQL & "WHERE customer_id = " & Me.customer_id

MyCommand.CommandText = strSQL
MyCommand.Connection = connUnifiedLiveryClass
MyCommand.ExecuteNonQuery()

Else
strSQL = ""
strSQL = strSQL & "INSERT INTO customer "
strSQL = strSQL & "( "
strSQL = strSQL & "customer_id, customer_number,
customer_type_id, customer_status_id, "
strSQL = strSQL & "surname, first_name, last_name,
middle_initial, suffix, address_1, "
strSQL = strSQL & "address_2, address_2_prefix, city, state,
zipcode, "

strSQL = strSQL & ") "
strSQL = strSQL & "VALUES "
strSQL = strSQL & "( "
strSQL = strSQL & "" & Me.customer_type_id & ", "
strSQL = strSQL & "" & Me.customer_status_id & ", "
strSQL = strSQL & "'" & Me.surname & "', "
strSQL = strSQL & "'" & Me.first_name & "', "
strSQL = strSQL & "'" & Me.last_name & "', "
strSQL = strSQL & "'" & Me.middle_initial & "', "
strSQL = strSQL & "'" & Me.suffix & "', "
strSQL = strSQL & "'" & Me.address_1 & "', "
strSQL = strSQL & "'" & Me.address_2 & "', "
strSQL = strSQL & "'" & Me.address_2_prefix & "', "
strSQL = strSQL & "'" & Me.city & "', "
strSQL = strSQL & "'" & Me.state & "', "
strSQL = strSQL & "'" & Me.zipcode & "', "

strSQL = strSQL & ") "

MyCommand.CommandText = strSQL
MyCommand.Connection = connUnifiedLiveryClass

MyCommand.ExecuteNonQuery()


'get the primary key back from the database
strSQL = ""
strSQL = strSQL & "SELECT customer_id "
strSQL = strSQL & "FROM customer "
strSQL = strSQL & "WHERE last_modified_date = '" &
strLastModifiedDate & "' "
strSQL = strSQL & "AND last_modified_by = " & glblUserID & " "

MyCommand.CommandText = strSQL
MyCommand.Connection = connUnifiedLiveryClass

rdrDataReader = MyCommand.ExecuteReader()

While rdrDataReader.Read
Me.customer_id = rdrDataReader.Item("customer_id")
End While

rdrDataReader.Close()
rdrDataReader = Nothing

End If

End Sub
-=-=-=-=-=-=-=-=-=

At runtime, however, I get the following error: 'An unhandled exception of
type "system.Data.SqlClient.SqlException" occurred in system.data.dll'

Additional info: "System error"


ANY HELP would be greatly appreciated. I am at my wit's end in getting this
class to function.

Thanks in advance for your time and assistance.
Best Regards
 
Hi Luis,

This error is usually caused by a malformed SQL statement. Have you tried
capturing the sql statement in the debugger just before execution and
running it standalone in QA ?

Cheers

Doug Forster
 
Back
Top