Error when connecting to Ms access database - Visual Basic Discussion Boards

Re: how to create PDF in vb.net?

Sathesh Sakthivel19-Jul-07 2:34

Sathesh Sakthivel

19-Jul-07 2:34

hope this will help you.

SSK.

Extracting a single record from a SQL database

aqzman_19-Jul-07 1:43

aqzman_

19-Jul-07 1:43

Hey guys, I'm having a little trouble with what I'm trying to do. I think there is just one or two lines of code that need to be entered (to what I already have), but I'm not exatly sure what it is.

What I'm trying to do extract one record from a SQL database, because right now I have the SQL statement in a combobox with visable disabled, and I know that's not correct.

Here is what I was thinking, and if this is totally wrong just let me know.

Sub Temp(ByVal myConnString As String)
    Dim mySelectQuery As String = "SELECT MAX(TicketID) AS Expr1 FROM tblTicket"
    Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
    Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery, myConnection)
    myConnection.Open()
    Dim myReader As Data.SqlClient.SqlDataReader
    myReader = myCommand.ExecuteReader()
    myReader.Close()
    myConnection.Close()
End Sub

The exact bit of data I want to extract is what would be in "Expr1".

I've tried several different things, as in...

Dim Number as Double
...
Number = myReader...

Hopefully there is just a few lines of code that can be entered to extract that data, if anyone can help me I'd really appreciate it.

Thanks,
aqzman

Re: Extracting a single record from a SQL database

Colin Angus Mackay19-Jul-07 1:54

Colin Angus Mackay

19-Jul-07 1:54

If you are only ever going to retrieve the first column of the first row (or that the data only has one row, and the row only has one column) then you can use ExecuteScalar() - That will save you faffing around with DataReaders.

Here is an updated snippet:

myConnection.Open()<br />
Dim someValue As Object = myCommand.ExecuteScalar();<br />
myConnection.Close()

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Re: Extracting a single record from a SQL database

aqzman_19-Jul-07 2:10

aqzman_

19-Jul-07 2:10

That worked like a charm!

Thanks a lot for the help!

Re: help

kubben19-Jul-07 1:46

kubben

19-Jul-07 1:46

I answered your question in the sql forum, please don't cross post and please put more meaningful subject lines in your posts.

Ben

more meaningful subject

Luc Pattyn19-Jul-07 2:43

Luc Pattyn

19-Jul-07 2:43

like this

Luc Pattyn

try { [Search CP Articles] [Search CP Forums] [Forum Guidelines] [My Articles] }
catch { [Google] }

Re: more meaningful subject

kubben19-Jul-07 2:51

kubben

19-Jul-07 2:51

I like it!

Error when connecting to Ms access database

Mekong River19-Jul-07 0:34

Mekong River

19-Jul-07 0:34

Hi, I got a problem that I could not solve when try to read my data from ms access database into the list box. I have the following code:
Under the form public class:

Private WithEvents cnnDonorDb As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\DICDonorDb\DICDornorDb.mdb'; Persist Security Info=False")

I add the following code to form load event to get data from the database to the list box:

Dim cmdOrgType As New OleDbCommand
        With cmdOrgType
            .Connection = cnnDonorDb
            .CommandType = CommandType.Text
            .CommandText = "SELECT * FROM tblOrgType.OrgType ORDER BY tblOrgType.OrgType;"
        End With

        ' Open the connection.
        cmdOrgType.Connection.Open()
        ' Execute the command to the return the list of organization type.
        cmdOrgType.ExecuteReader() ' HERE IS THE PLACE THAT I GET AN ERROR
        Dim drdResult As OleDbDataReader

        ' Get the list of organization type and put it in the list box.
        While drdResult.Read
            For colOrgType As Integer = 0 To drdResult.FieldCount - 1
                lsbOrgInfo.Items.Add(drdResult(colOrgType).ToString)
            Next

        End While
        ' Close the result.
        drdResult.Close()
        ' Close the connection
        cmdOrgType.Connection.Close()

I got an error as the following message:

Could not find file 'C:\Documents and Settings\roathkanel\My Documents\Visual Studio 2005\Projects\DIC Donor Database\DIC Donor Database\bin\Debug\tblOrgType.mdb'.

I really don't understand why the database path in this message is difference from database path in C: drive. As you can see the path in the connection string is C:\DICDonorDb\DICDornorDb.mdb. I'm sure that the file is located in this folder because I didn't type it, i copy the string path and file name into my code editor.

Please help me to solve this problem. I'm using VB 2005.
Thank in advance!!!

Re: Error when connecting to Ms access database

Dave Kreskowiak19-Jul-07 3:44

Dave Kreskowiak

19-Jul-07 3:44

There's a few problems with this...

First, this:

Private WithEvents cnnDonorDb As New System.Data.OleDb.OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\DICDonorDb\DICDornorDb.mdb';" & _
    "Persist Security Info=False")

doesn't need the With Events clause. Well, no unless you're going to handle any of the three events that it exposes. Just about noone does...

Second, in your SQL statement, you specified the name of the database in your table spec. Actually, you treated the name of the table as a database name:

SELECT * FROM tblOrgType.OrgType ORDER BY tblOrgType.OrgType;

assuming that tblOrgType is a table and OrgType is a column in that table:

SELECT OrgType FROM tblOrgType ORDER BY OrgType

Altogether, your code should read more like this: Do NOT hold open a database connection object for the life of your application.

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\DICDonorDb\DICDornorDb.mdb""; Persist Security Info=False;"
Dim conn As New OldDbConnection(connString)
Dim cmdOrgType As New OleDbCommand

With cmdOrgType
    .Connection = cnnDonorDb
    .CommandType = CommandType.Text
    .CommandText = "SELECT OrgType FROM tblOrgType ORDER BY OrgType"
End With

' Open the connection.
cmdOrgType.Connection.Open()

' Execute the command to the return the list of organization type.
Dim drdResult As OleDbDataReader
drdResult = cmdOrgType.ExecuteReader()

' Get the list of organization type and put it in the list box.
While drdResult.Read
    For colOrgType As Integer = 0 To drdResult.FieldCount - 1
        lsbOrgInfo.Items.Add(drdResult(colOrgType).ToString)
    Next
End While

' Close the result.
drdResult.Close()

' Close the connection
cmdOrgType.Connection.Close()

This, of course, does NOT mean this is good code. There's no exception handling in your code and it makes a bunch of assumptions where it's running in a "perfect world" where everything work the way it should.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Error when connecting to Ms access database

Mekong River19-Jul-07 17:44

Mekong River

19-Jul-07 17:44

Hi, thank you for your comment and the previous problem are solved based on your code. But another problem is occure in the above code:

...
cmdOrgType.Connection.Open()
Dim drdResult As OleDbDataReader
drdResult = cmdOrgType.ExecuteReader()
While drdResult.Read   ' Here is the problem occure.
For colOrgType As Integer = 0 To drdResult.FieldCount - 1          lsbOrgInfo.Items.Add(drdResult(colOrgType).ToString)
Next
End While
drdResult.Close()
cmdOrgType.Connection.Close()

Here is an error message:
Variable 'drdResult' is used before it has been assigned a value. A null reference exception could result at runtime

I think that the reason of cause the problem because my table do not have any record so the data reader object could not read the value. If it is possible, could you please let me know the sample code to check whether there is an existing record in the table?

Thank in advance!!!

Re: Error when connecting to Ms access database

Dave Kreskowiak20-Jul-07 2:28

Dave Kreskowiak

20-Jul-07 2:28

You can check to see if a DataReader was actually returned with:

drdResult = cmdOrgType.ExecuteReader()
If Not drdResult Is Nothing Then
    While ...

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Error when connecting to Ms access database

Mekong River22-Jul-07 15:39

Mekong River

22-Jul-07 15:39

Thank you very much for your help. I will try this code.

How To AOP IN VB.NET

tomaka18-Jul-07 23:43

tomaka

18-Jul-07 23:43

Hi

I am vb.net developer i want to use aop (Aspect Oriented Programming) in visual basic .net
How to Use it ??

Thank you Big Grin | :-D

Worachat

Re: How To AOP IN VB.NET

Dave Kreskowiak19-Jul-07 3:29

Dave Kreskowiak

19-Jul-07 3:29

Next time, try Google before asking such a broad question. Here, allow me ... Google results for "vb.net aspect oriented programming"[^].

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Problem on Executing application path in VB6.0

Srinivas Kaparthi18-Jul-07 23:31

Srinivas Kaparthi

18-Jul-07 23:31

Please let me know the name of constant identifier for executing applications path in Visual basic 6.0

Re: Problem on Executing application path in VB6.0

Navneet Hegde19-Jul-07 2:38

Navneet Hegde

19-Jul-07 2:38

Try
App.Path

Develop2Program & Program2Develop

Re: Problem on Executing application path in VB6.0

Srinivas Kaparthi21-Jul-07 1:02

Srinivas Kaparthi

21-Jul-07 1:02

Thanks!

Access form1 from form2.

KOKEMO18-Jul-07 23:23

KOKEMO

18-Jul-07 23:23

hi

I have some forms and these forms are actually related. what i want to do is that in form2 ,i want to access and modify the contents of the text boxes in form1, while i am still in form2. how can i do this ? do i have to declare them all as Public shared in from1.

Thank You Poke tongue | ;-P

Kokemo

|-|■~F~|-|■

Re: Access form1 from form2.

Colin Angus Mackay18-Jul-07 23:39

Colin Angus Mackay

18-Jul-07 23:39

KOKEMO wrote:
how can i do this ?

See Passing Values between Forms[^]

KOKEMO wrote:
do i have to declare them all as Public shared in from1.

That would be unwise.

Re: Access form1 from form2.

Naji El Kotob19-Jul-07 0:07

Naji El Kotob

19-Jul-07 0:07

Hi,

In form2

Private m_f As Form1

Public Sub New(ByVal f As Form1)
Call Me.New
m_f = f
End Sub

Public Sub New()
InitializeComponent()
End Sub
...
Now, you can use m_f to refer to form1

in Form1
Dim f As New Form2(Me)
f.Show

regards Wink | ;)

NajiCo http://www.InsideVB.NET[^]

It's nice 2b important, but it's more important 2b nice...

Re: Access form1 from form2.

Dave Kreskowiak19-Jul-07 3:13

Dave Kreskowiak

19-Jul-07 3:13

This was brought up yesterday.

You do NOT modify the controls on Form1 from Form2. Form2 shouldn't know anything about Form1, nor should it care. All it should care about is the data that it's supposed to manipulate.

If you're showing Form2 using ShowDialog(), then Form1 creates an instance of Form2 and waits for Form2 to be dismissed. Form2 exposes any data that it should as public properties or as some data object. Form1, when it gets control back, looks at the data in Form2 properties or in the data object that it modified and then modifies its own controls accordingly.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Access form1 from form2.

Paul Conrad19-Jul-07 9:34

Paul Conrad

19-Jul-07 9:34

Dave Kreskowiak wrote:
do NOT modify the controls on Form1 from Form2

Seems like there has been a rash of these types of questions lately Suspicious | :suss:

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Re: Access form1 from form2.

Dave Kreskowiak19-Jul-07 15:55

Dave Kreskowiak

19-Jul-07 15:55

Makes me think there's a new school year somewhere... Unsure | :~

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Access form1 from form2.

Paul Conrad19-Jul-07 16:02

Paul Conrad

19-Jul-07 16:02

Ohhhhhh joy Roll eyes | :rolleyes:

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

easy way to print datagrid ?

Mr.Kode18-Jul-07 23:16

Mr.Kode

18-Jul-07 23:16

i searched alot about how to print datagrid and all i get was alot of code and classes .

i have a populated datagrid
all i want is to print the datagrid as its , i don't want to manipulate the cells size or fonts or any thing ..... just print it completly

note : i had tried to print it but the printed part was the monitor showing part ... i need to print it completly

and thanks for all

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.