Error in Connection String

[yousuf42]

Dear All,

I'm getting error with the following code. can anyone shortout the problem please. Your help is highly appreciated.

Public Function GetProductDetails(ByVal productID As Integer) As ProductsDetails
' Create Instance of Connection and Command Object
Dim techConnection As OleDbConnection = New OleDbConnection(strConn)
Dim techCommand As OleDbCommand = New OleDbCommand("techProductDetails", techConnection)
' Mark the Command as a SPROC
techCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterProductID As OleDbParameter = New OleDbParameter("@ProductID", OleDbType.Integer, 10)
parameterProductID.Value = productID
techCommand.Parameters.Add(parameterProductID)
Dim parameterUnitPrice As OleDbParameter = New OleDbParameter("@UnitPrice", OleDbType.Currency, 8)
parameterUnitPrice.Direction = ParameterDirection.Output
techCommand.Parameters.Add(parameterUnitPrice)
 
Dim parameterProductName As OleDbParameter = New OleDbParameter("@ProductName", OleDbType.VarChar, 50)
parameterProductName.Direction = ParameterDirection.Output
techCommand.Parameters.Add(parameterProductName)
 
' Open the connection and execute the Command
techConnection.Open()
techCommand.ExecuteNonQuery()
techConnection.Close()
' Create and Populate ProductDetails Struct using
' Output Params from the SPROC
Dim myProductDetails As ProductsDetails = New ProductsDetails
myProductDetails.ProductID = CStr(parameterProductID.Value)
myProductDetails.ProductName = CStr(parameterProductName.Value)
myProductDetails.UnitPrice = CType(parameterUnitPrice.Value, Decimal)
Return myProductDetails
End Function

and calling procedure of this function as

Private Sub PopulateProductData()
Dim ProdID As Integer = CInt(txtProdID.Text)

 
Try
Dim ProductData As TechManagement.DBComponents.ProductDB = New TechManagement.DBComponents.ProductDB
Dim myProductDetails As TechManagement.DBComponents.ProductsDetails = ProductData.GetProductDetails(ProdID)
txtProdName.Text = myProductDetails.ProductName
 
MsgBox(myProductDetails.ProductName)
 

Catch e As OleDbException
MsgBox(e.Message)
End Try
End Sub

 

[yousuf42]

I'm struck with this problem for the past several days

Dear cjard,

Thanks for your kind help and giving valuable time. The only problem to upgrade to the 2005 express edition is I have crystal reports in my project and other features which I can't adopt with the free version.

Also I have done all as your have suggested(removing unwanted codes and parameters) but still the same error message appears. as below

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

I'm struck with this problem for the past several days.

 

[cjard]

Yep, thats because the code is really, really screwed up and there are old half-compiled objects loitering in the project output folders that havent been cleaned up and are being re-used by the interpreter

Clean the project, and have a read of the DW1 link in my signature for info on how we do data access in .net 1.1 - cause it aint like what you have here (i've pointed out a few of the errors but really, it's just like its been copy-pasted from somewhere else and never edited; its broken!)

 

[cjard]

Ps; that message really doesnt help. Thats like saying "It is giving error"

We know that.. what we want to know is what the error message is! (And no.. "An unhandled exception occurred in...." is NOT an error message

If this thing were a bus timetable, then that "An unhandled exception occurred in...." is about as useful as the header that says "This is the Bus TimeTable for London" -- it tells us *nothing*. When we want to find the time of the bus, we dont look at the leaflet header.. we look a tthe detail...

 

[yousuf42]

Problem solved. But Is it Right?

Hi cjard and all,

Thanks for all your kind help. now I solved the problem. But pls tell me is it the right way.

Public Function GetProductDetail(ByVal ProductID As String) As DataSet
Dim techConnection As OleDbConnection = New OleDbConnection(strConn)
Dim techDataAdapter As OleDbDataAdapter
Dim dsProductDetail As DataSet
 
Try
 
Dim strSQL As String = _
"SELECT * FROM Products WHERE ProductID = @ProductID"
 
Dim techCommand As New OleDbCommand(strSQL, techConnection)
techCommand.Parameters.Add("@ProductID", OleDbType.VarChar, 20, "ProductID").Value = ProductID
techDataAdapter = New OleDbDataAdapter(techCommand)
Dim techCommandBuilder As New OleDbCommandBuilder(techDataAdapter)
' Create a new DataSet and fill its first DataTable.
dsProductDetail = New DataSet
techDataAdapter.Fill(dsProductDetail, "ProductDetail")
Catch EX As OleDbException
MsgBox(EX.Message)
End Try
 
' Return the filled DataSet
Return dsProductDetail
End Function

and calling sub

Sub GetProductDetail()
Try
Dim GetProductData As TechManagement.DBComponents.ProductDB = New TechManagement.DBComponents.ProductDB
 
dsProductDetail = GetProductData.GetProductDetail(txtProdID.Text)
txtProdName.Text = dsProductDetail.Tables(0).Rows(0).Item("ProductName").ToString
Catch ex As Exception
 
 
MsgBox(ex.Message)
End Try
 
End Sub

 

[cjard]

looks good to me! one last thing.. MsgBox is old vb6 style. in .net that function is called MessageBox.Show.

also technically i wouldnt use a dataset here because a dataset is a collection of data tables whereas because youre only using one table I would just make it a datatable that is filled and returned..

 

[yousuf42]

Thanks to everybody now it works with the following modified code

Dear cjard and all,

Thanks to everybody now the code works:--- here I provide it in order to help others who have similar problem

Public Function GetProductDetail(ByVal ProductID As String) As DataTable
Dim techConnection As OleDbConnection = New OleDbConnection(strConn)
Dim techDataAdapter As OleDbDataAdapter
Dim dtProductDetail As DataTable
 
Try
Dim strSQL As String = _
"SELECT * FROM Products WHERE ProductID = @ProductID"
 
Dim techCommand As New OleDbCommand(strSQL, techConnection)
techCommand.Parameters.Add("@ProductID", OleDbType.VarChar, 20, "ProductID").Value = ProductID
techDataAdapter = New OleDbDataAdapter(techCommand)
Dim techCommandBuilder As New OleDbCommandBuilder(techDataAdapter)
' Create a new DataTable and fill its first Data.
dtProductDetail = New DataTable
techDataAdapter.Fill(dtProductDetail)
Catch EX As Exception
MsgBox(EX.Message)
End Try
 
' Return the filled DataTable
Return dtProductDetail
End Function

Calling Sub---

Sub GetProductDetail()
Try
Dim GetProductData As TechManagement.DBComponents.ProductDB = New TechManagement.DBComponents.ProductDB
 
dtProductDetail = GetProductData.GetProductDetail(txtProdID.Text)
txtProdName.Text = dtProductDetail.Rows(0).Item("color").ToString
Catch ex As Exception
 
MsgBox(ex.Message)
End Try

Thanks

 

[yousuf42]

Thanks Mr.cjard and others this code now works