Row doesn't exist problem

[Mike]

Hi

I've written my first asp.net page below. It queries an Access database with
one table consisting of two columns - a username and a server name. The
users enter their login name and the page queries the database and then
directs them to the correct server. This works fine as long as their name
actually exists in the database. If it doesn't the the page fails at the

server = dataSet.Tables(0).Rows(0).Item(0).ToString()

line saying row doesn't exist. My question is how can I handle this? If the
usersname is not in the database I would then like the server string to be
set to a predefined value such as 'noserver'.

Cheers

Mike

Sub Page_Load()

If Page.IsPostback

Dim mailserver As String
mailserver = MyQueryMethod(txtName.Text)

Select Case mailserver
Case "Bob"
Response.Redirect("http://localhost/mailserver1")
Case "Bill"
Response.Redirect("http://localhost/mailsever2")
Case Else
Response.Redirect("http://localhost/nomailserver")
End Select

End If

End Sub

Function MyQueryMethod(ByVal user As String) As String
Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=C:\Documents an"& _
"d Settings\Mike\My Documents\BegASPNET11\Ch01\users.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [Users].[Mailserver] FROM
[Users] WHERE ([Users].[User] = @user)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_user As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_user.ParameterName = "@user"
dbParam_user.Value = user
dbParam_user.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_user)

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Dim server As String
server = dataSet.Tables(0).Rows(0).Item(0).ToString()

Return server
End Function

 

[Chris R. Timmons]

Hi

I've written my first asp.net page below. It queries an Access
database with one table consisting of two columns - a username
and a server name. The users enter their login name and the page
queries the database and then directs them to the correct
server. This works fine as long as their name actually exists in
the database. If it doesn't the the page fails at the

server = dataSet.Tables(0).Rows(0).Item(0).ToString()

line saying row doesn't exist. My question is how can I handle
this? If the usersname is not in the database I would then like
the server string to be set to a predefined value such as
'noserver'.

Mike,

If dataSet.Tables(0).Rows.Count > 0 Then
Return dataSet.Tables(0).Rows(0).Item(0).ToString()
Else
Return "noserver"
End If


Hope this helps.

Chris.

 

[Ken Cox [Microsoft MVP]]

Hi Mike, you want to test if the value is null using the IsDBNull function.
Here's an example:

Private Sub Page_Load _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim ds As New DataSet
Dim dt As New DataTable
Dim server As String
ds.Tables.Add(CreateDataSource())
If IsDBNull(ds.Tables(0).Rows(0).Item(0)) Then
server = "Not in database"
Else
server = ds.Tables(0).Rows(0).Item(0).ToString()
End If
Response.Write(server)
End Sub

Function CreateDataSource() As DataTable
Dim dt As New DataTable
Dim dr As DataRow
dt.Columns.Add(New DataColumn("IntegerValue", GetType(Int32)))
dt.Columns.Add(New DataColumn("StringValue", GetType(String)))
dt.Columns.Add(New DataColumn("CurrencyValue", GetType(Double)))
dt.Columns.Add(New DataColumn("Boolean", GetType(Boolean)))
Dim i As Integer
For i = 0 To 8
dr = dt.NewRow()
dr(0) = i
dr(1) = "Item " + i.ToString()
dr(2) = 1.23 * (i + 1)
dr(3) = (i = 4)
dt.Rows.Add(dr)
Next i
Return dt
End Function


Does this help?

Ken
Microsoft MVP [ASP.NET]
Toronto

Hi

I've written my first asp.net page below. It queries an Access database
with
one table consisting of two columns - a username and a server name. The
users enter their login name and the page queries the database and then
directs them to the correct server. This works fine as long as their name
actually exists in the database. If it doesn't the the page fails at the

server = dataSet.Tables(0).Rows(0).Item(0).ToString()

line saying row doesn't exist. My question is how can I handle this? If
the
usersname is not in the database I would then like the server string to be
set to a predefined value such as 'noserver'.

Cheers

Mike

Sub Page_Load()

If Page.IsPostback

Dim mailserver As String
mailserver = MyQueryMethod(txtName.Text)

Select Case mailserver
Case "Bob"
Response.Redirect("http://localhost/mailserver1")
Case "Bill"
Response.Redirect("http://localhost/mailsever2")
Case Else
Response.Redirect("http://localhost/nomailserver")
End Select

End If

End Sub

Function MyQueryMethod(ByVal user As String) As String
Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=C:\Documents an"& _
"d Settings\Mike\My Documents\BegASPNET11\Ch01\users.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [Users].[Mailserver] FROM
[Users] WHERE ([Users].[User] = @User)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_user As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_user.ParameterName = "@User"
dbParam_user.Value = user
dbParam_user.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_user)

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Dim server As String
server = dataSet.Tables(0).Rows(0).Item(0).ToString()

Return server
End Function

 

[Mike Varley]

Thanks Chris

Mike,

If dataSet.Tables(0).Rows.Count > 0 Then
Return dataSet.Tables(0).Rows(0).Item(0).ToString()
Else
Return "noserver"
End If


Hope this helps.

Chris.

 

[Mike Varley]

Thanks Ken

Hi Mike, you want to test if the value is null using the IsDBNull function.
Here's an example:

Private Sub Page_Load _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim ds As New DataSet
Dim dt As New DataTable
Dim server As String
ds.Tables.Add(CreateDataSource())
If IsDBNull(ds.Tables(0).Rows(0).Item(0)) Then
server = "Not in database"
Else
server = ds.Tables(0).Rows(0).Item(0).ToString()
End If
Response.Write(server)
End Sub

Function CreateDataSource() As DataTable
Dim dt As New DataTable
Dim dr As DataRow
dt.Columns.Add(New DataColumn("IntegerValue", GetType(Int32)))
dt.Columns.Add(New DataColumn("StringValue", GetType(String)))
dt.Columns.Add(New DataColumn("CurrencyValue", GetType(Double)))
dt.Columns.Add(New DataColumn("Boolean", GetType(Boolean)))
Dim i As Integer
For i = 0 To 8
dr = dt.NewRow()
dr(0) = i
dr(1) = "Item " + i.ToString()
dr(2) = 1.23 * (i + 1)
dr(3) = (i = 4)
dt.Rows.Add(dr)
Next i
Return dt
End Function


Does this help?

Ken
Microsoft MVP [ASP.NET]
Toronto

Hi

I've written my first asp.net page below. It queries an Access database
with
one table consisting of two columns - a username and a server name. The
users enter their login name and the page queries the database and then
directs them to the correct server. This works fine as long as their name
actually exists in the database. If it doesn't the the page fails at the

server = dataSet.Tables(0).Rows(0).Item(0).ToString()

line saying row doesn't exist. My question is how can I handle this? If
the
usersname is not in the database I would then like the server string to be
set to a predefined value such as 'noserver'.

Cheers

Mike

Sub Page_Load()

If Page.IsPostback

Dim mailserver As String
mailserver = MyQueryMethod(txtName.Text)

Select Case mailserver
Case "Bob"
Response.Redirect("http://localhost/mailserver1")
Case "Bill"
Response.Redirect("http://localhost/mailsever2")
Case Else
Response.Redirect("http://localhost/nomailserver")
End Select

End If

End Sub

Function MyQueryMethod(ByVal user As String) As String
Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=C:\Documents an"& _
"d Settings\Mike\My Documents\BegASPNET11\Ch01\users.mdb"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [Users].[Mailserver] FROM
[Users] WHERE ([Users].[User] = @User)"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_user As System.Data.IDataParameter = New
System.Data.OleDb.OleDbParameter
dbParam_user.ParameterName = "@User"
dbParam_user.Value = user
dbParam_user.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_user)

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Dim server As String
server = dataSet.Tables(0).Rows(0).Item(0).ToString()

Return server
End Function