Duppypog
Mon Aug 18 15:56:22 CDT 2003
Thanks for all your help. I will post the procedure for your review.
Lynnette
This code works:
Private Sub Datafill()
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim strSQL As String
Select Case Me.rblView.SelectedItem.Value
Case "1st Shift"
strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='1' ORDER BY exempt DESC,
LastWorked ASC, HireDate DESC"
Case "2nd Shift"
strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='2' ORDer BY exempt DESC,
LastWorked ASC, HireDate DESC"
Case "3rd Shift"
strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='3' ORDER BY exempt DESC,
LastWorked ASC, HireDate DESC"
End Select
Dim cnn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Inetpub\wwwroot\ASPdotNET\CORmandOT\db\NewSecure
CORMandatoryOTList.mdb; " & _
"Jet OLEDB:System
Database=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corrections.mdw; " & _
"User id=dbadmin; Password=jaxkdbadmin;")
Try
cnn.Open()
da = New OleDbDataAdapter(strSQL, cnn)
ds = New DataSet()
da.Fill(ds, "tblVolunteer_list")
Session("DataView") = ds.Tables("tblVolunteer_list").DefaultView()
Catch
'lblError.Text = e.ToString
Finally
cnn.Close()
cnn.Dispose()
End Try
End Sub
This code does NOT work (but I don't know why):
Private Sub Datafill()
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim strSQL As String
Select Case Me.rblView.SelectedItem.Value
Case "1st Shift"
strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='1' ORDER BY exempt DESC,
LastWorked ASC, HireDate DESC"
Case "2nd Shift"
strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='2' ORDer BY exempt DESC,
LastWorked ASC, HireDate DESC"
Case "3rd Shift"
strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='3' ORDER BY exempt DESC,
LastWorked ASC, HireDate DESC"
End Select
Dim DataPath As String =
System.Web.HttpContext.Current.Server.MapPath("NewSecure
CORMandatoryOTList.mdb") 'also tried "/CORmandOT/NewSecure
CORMandatoryOTList.mdb"
Dim cnn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & DataPath & "; " & _
"Jet OLEDB:System
Database=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corrections.mdw; " & _
"User id=dbadmin; Password=jaxkdbadmin;")
Try
cnn.Open()
da = New OleDbDataAdapter(strSQL, cnn)
ds = New DataSet()
da.Fill(ds, "tblVolunteer_list")
Session("DataView") = ds.Tables("tblVolunteer_list").DefaultView()
Catch
'lblError.Text = e.ToString
Finally
cnn.Close()
cnn.Dispose()
End Try
End Sub
<andrew.swanson@ujs.state.sd.us> wrote in message
news:09ba01c3659a$e9fe4530$a501280a@phx.gbl...
> Lynnette,
>
> If you didn't have any problem with the connection and if
> you didn't receive any data back from your database then
> this could only be one thing...Your Data Selection method.
> Check to make sure your selection criteria is set
> correctly. Your Dataset is empty due to your selection
> criteria you've set did not match what is in your
> database...Recheck your SQL Selection ceriteria...Any
> further problems, you might want to post your SQL
> selection command...
>
> Andy S.
>
>
> >-----Original Message-----
> >Andy, Thanks. I had to change line 3 to "Data Source="
> instead of just
> >"Source=". I need to change the data source to point to
> a virtual
> >directory. I tried this code and it returned no error,
> but no data either.
> >Can you (or anyone) spot the problem?
> >
> >Dim cnn As OleDbConnection = New
> >OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
> >
> >"Data Source=" &
> >System.Web.HttpContext.Current.Server.MapPath
> ("/CORmandOTweb/NewSecure
> >CORMandtoryOTList.mdb") & "; " & _
> >
> >"Jet OLEDB:System
> >Database=c:\inetpub\wwwroot\aspdotnet\cormandot\db\correc
> tions.mdw; " & _
> >
> >"User id=dbadmin; Password=jaxkdbadmin;")
> >
> >
> >Thanks again,
> >Lynnette
> >
> >
> >
> >
> >"Andy S." <andrew.swason@ujs.state.sd.us> wrote in
> message
> >news:0f3501c36276$cf3de970$a601280a@phx.gbl...
> >> Sorry Lynnette, I had a syntax error also...so here is
> >> the repost...
> >>
> >> Dim strConnection as String = _
> >> "Provider=Microsoft.Jet.OLEDB.4.0; " & _
> >> "Source=\your path\yourdb.mdb; " & _
> >> "Jet OLEDB:System Database=\pathto.mdw\yourdb.mdw; "
> & _
> >> "User id=yourid; Password=yourpassword;"
> >>
> >> Sorry about that...
> >>
> >> Andy S.
> >> >-----Original Message-----
> >> >Lynnette,
> >> >
> >> >The following syntax should work...
> >> >
> >> >Dim strConnection as String = _
> >> > "Provider=Microsoft.Jet.OLEDB.4.0; " & _
> >> > "Source=\your path\yourdb.mdb; " & _
> >> > "Jet OLEDB:System Database=\path
> >> to .mdw\yourdb.mdw; "
> >> > "User id=yourid; Password=yourpassword;"
> >> >
> >> >Looking at the syntax you've written, you had a
> >> semicolon
> >> >missing after password...
> >> >
> >> >Good luck...
> >> >
> >> >Andy S.
> >> >>-----Original Message-----
> >> >>Hi
> >> >>Check with the following Connection Strings:
> >> >>
> >> >> Workgroup (system database):
> >> >>"Provider=Microsoft.Jet.OLEDB.4.0;Data
> >> >>Source=\somepath\mydb.mdb;Jet OLEDB:System
> >> >>Database=system.mdw;","admin", ""
> >> >>
> >> >>
> >> >> With password:
> >> >>"Provider=Microsoft.Jet.OLEDB.4.0;Data
> >> >>Source=\somepath\mydb.mdb;Jet OLEDB:Database
> >> >>Password=MyDbPassword;","admin", ""
> >> >>
> >> >>
> >> >>HTH
> >> >>Ravikanth[MVP]
> >> >>
> >> >>
> >> >>>-----Original Message-----
> >> >>>I've searched through several forums without
> finding a
> >> >>definitive answer to
> >> >>>this question:
> >> >>>
> >> >>>What is the proper syntax for a connection string
> that
> >> >>is connecting to an
> >> >>>Access 97 db with workgroup security applied?
> >> >>>
> >> >>>I tried the following. I don't get an error, but it
> >> >also
> >> >>displays NO
> >> >>>results:
> >> >>>
> >> >>>Public Shared ReadOnly OleDbConnectionString As
> String
> >> =
> >> >>>"Provider=Microsoft.Jet.OLEDB.4.0;Data
> >> >>Source=DBQ=c:\CORmandOTweb\NewSecure
> >> >>>CORMandatoryOTList.mdb
> >>
> >>>SystemDB=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corr
> >> e
> >> >c
> >> >>tions.mdw;UID=usern
> >> >>>ame;PWD=anything"
> >> >>>
> >> >>>Also tried:
> >> >>>
> >> >>>Dim cnn As OleDbConnection = New
> >> >>>OleDbConnection
> >> >>("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security
> >> >>>Info=false;Data Source=" &
> >> >>>System.Web.HttpContext.Current.Server.MapPath
> >> >>("/CORmandOTweb/NewSecure
> >> >>>CORMandtoryOTList.mdb") & "; Jet OLEDB:System
> >>
> >>>Database=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corr
> >> e
> >> >c
> >> >>tions.mdw;UserID=us
> >> >>>ername;Password=anything")
> >> >>>
> >> >>>
> >> >>>All help will be greatly appreciated!
> >> >>>Lynnette
> >> >>>
> >> >>>
> >> >>>
> >> >>>.
> >> >>>
> >> >>.
> >> >>
> >> >.
> >> >
> >
> >
> >.
> >