Help reading access and sql databases

[Jnuw]

Hello all.

I'm very new to VB.Net, so please forgive me if I'm missing something simple. I have searched this forum, but can't seem to get my script working.

I would like to write a program (console app I believe would be fine) that I can launch and will simply read one field from either an access or sql db into a variable, and then write it into a text file to be read with another program. I'm using a sql db for testing below.

I have this so far:

[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.OleDb
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Console
[/SIZE][SIZE=2][COLOR=#0000ff]Module[/COLOR][/SIZE][SIZE=2] Module1
[/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Main()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myDataReader [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataReader
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myOleDbConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbConnection
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myOleDbCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbCommand
myOleDbConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection("server=ServerName;uid=sa;pwd=Password;database=Test;provider=sqloledb")
myOleDbCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand("SELECT TestField FROM TestTable", myOleDbConnection)
myOleDbConnection.Open()
myDataReader = myOleDbCommand.ExecuteReader()
[/SIZE][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] (myDataReader.Read())
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] (myDataReader.IsDBNull(4)) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]Console.Write("N/A" + Chr(10))
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Loop
[/COLOR][/SIZE][SIZE=2]myDataReader.Close()
myOleDbConnection.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Module[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

My problem is this, it errors on the "If (myDataReader.IsDBNull(4)) Then" line. Any help you all could provide would be most appreciated, thank you.

 

[prav_roy]

you have not mentioned wht error u r getting, and i assume isdbnull(4) is the forth column in ur datareader so here wht i have used try this.. it might help you..
If myDataReader(4) Is System.DBNull.Value = True Then
your code
End If

 

[Jnuw]

Sorry, its been a long night. Error message is, "An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll
Additional information: Index was outside the bounds of the array."

I really am new at this, so any examples would be great. Just need the syntax to read a database into a variable. Thanks all.

 

[Jnuw]

Ok, I think I'm on the right track now. I replaced everything below "myDataReader = myOleDbCommand.ExecuteReader()"

with:
myDataReader.Read()
IDNum = (myDataReader(0))

Since I am only looking for 1 piece of data, I think this should work. I verified that IDNum is getting set to the correct data from the database.

Thanks all for looking.

 

[jmcilhinney]

Sounds like a data access question to me. I think I'll move it to that forum.

 

[Jnuw]

Sounds like a data access question to me. I think I'll move it to that forum.

Thanks much. Sorry for the mis-post.

 

[jmcilhinney]

If you only want a single value then ExecuteScalar is the way to go.