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

 

[CygNuS]

' Create Instance of Connection and Command Object
Dim techConnection As OleDbConnection = New OleDbConnection(strConn)

Error probably occurs here? Could you check with breakpoints?
If so, how and where is variable 'strConn' initialized?

 

[yousuf42]

It is in a Module

Connection String is in a public module as here

[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Module[/COLOR][/SIZE][SIZE=2] ConnectionStringMDL[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Const[/COLOR][/SIZE][SIZE=2] SQL_CONNECTION_STRING [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\store.mdb;"[/SIZE]
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] DidPreviouslyConnect [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] strConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = SQL_CONNECTION_STRING[/SIZE]
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Module[/COLOR][/SIZE]

 

[cjard]

Make a new file on the hard disk called A.UDL
Double click it
Set all the relevant properties in there for your database
Say OK
Now open the file in notepad
There is your valid connection string!

 

[yousuf42]

Dear cjard,

Thanks for your reply and others

The strConn(Connection String) Does not have problem with other functions. It works for all other functions except for this one.

any clue pls?

Thanks

 

[cjard]

whats the point of making

Public strConn As String = SQL_CONNECTION_STRING

Why not just directly use SQL_CONNECTION_STRING?

New OleDbConnection(SQL_CONNECTION_STRING)



Did you try the advice in the post above?

 

[yousuf42]

Yes I tried it. It does not work.

But it works for other functions in the project. I don't know what is the problem with this function

 

[yousuf42]

err_ mesg I don know the cause!!!!!!

Dear All,

I have tried to figure out the problem by "try catch " method. Now the following message appears as attached image, when I run the code. What is the problem can any one explain please.

Thanks in advance

 

[CygNuS]

We might narrow down the problem if you stepped through the code while running to see on what line the error occurs exactly.

Anyway, Google gives a lot of hits on that error:

Possible causes:
http://www.adopenstatic.com/faq/80040e21.asp

If none of that helps locate the problem then there's probably something wrong in your registry:
http://support.microsoft.com/kb/269495

 

[cjard]

upload your project, without the BIN or OBJ folders, in a zip file

 

[yousuf42]

Dear cjard,and all,

I removed the reference and project of my data access layer and reattached that project and rebuilt the solution. Now that message has gone. Now I have another error. the code stops on connection.open. The project is apploaded here. Please check and adVice, what is wrong here.

Thanks a lot in advance

 

[cjard]

please post a solution file that names both projects used.. additionally remove any references to third party controls that I dont have (i.e. all of them)

 

[yousuf42]

Sorry for that

Dear cjard and all,

Here I appload it again. If it is not running just add reference to the DAL (from BIN directory of Data Access Layer)after building the project.

Thanks for giving your valuable time.

 

[cjard]

by the way, here is an error:

        Try

            ' Open the connection and execute the Command
            techConnection.Open()
            techCommand.ExecuteNonQuery()
            techDataAdapter.Fill(techDataSet)
            Dim myProductDetails As ProductsDetails = New ProductsDetails


            myProductDetails.ProductID = CStr(parameterProductID.Value)
            myProductDetails.ProductName = CStr(parameterProductName.Value)
            myProductDetails.UnitPrice = CType(parameterUnitPrice.Value, Decimal)
            [B][COLOR="Red"]Return GetProductDetails[/COLOR][/B]

            'techConnection.Close()

        Finally

 

[cjard]

There's something really, really screwed up with your project. I changed strConn to SQL_CONNECTION_STRING and renamed that constant to just CONNECTION_STRING throughout the entire project..

The immediate window said this:

?ConnectionStringMDL.CONNECTION_STRING
'CONNECTION_STRING' is not a member of 'ConnectionStringMDL'.

?ConnectionStringMDL.CONNECTION_ERROR_MSG
"To run this sample, you must have SQL or MSDE with the Northwind database installed. For instructions on installing MSDE, view the ReadMe file."


I used intellisense for both these - but the interpreter could only find the second one. Yick. I exited the app and chose Clean to completely remove all old compile output etc.. and then it started working.

I now get the error:

System.Data.OleDb.OleDbDataAdapter internal error: invalid parameter accessor: 2 BADBINDINFO

You get this because your data access is not correct.
The sql query:
"Select ProductID, ProductName from Products where ProductID = @ProductID"


does NOT need parameters for the two fields in the query:

[SIZE=1][COLOR=#a31515][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] parameterUnitPrice [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbParameter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter([/SIZE][SIZE=2][COLOR=#a31515]"@UnitPrice"[/COLOR][/SIZE][SIZE=2], [SIZE=2]OleDbType.Currency, 8)
[/SIZE][/SIZE][SIZE=2][SIZE=2]parameterUnitPrice.Direction = ParameterDirection.Output[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] parameterProductName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbParameter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter([/SIZE][SIZE=2][COLOR=#a31515]"@ProductName"[/COLOR][/SIZE][SIZE=2], OleDbType.VarChar, 50)[/SIZE]
[SIZE=2]parameterProductName.Direction = ParameterDirection.Output[/SIZE][/SIZE][/COLOR][/SIZE]

further, output parameters are ONLY used for stored procedures. this query doesnt target one. it also doesnt need a parameter for unit price because it never ever mentions unit price..


Infact.. this is all, and i mean all, screwed up.. You really dont do data access like this. Take a read of the DW1 link in my signature. While not as comprehensive as DW2, its stil lpretty good.

Additionally, I think you should upgrade to VB2005 - it has some good features to help you write data access layers, and it catches erroneous things in your code that 2003 overlooks. Given that it's free, that's also a bonus