Log inUsernamePassword
Log me on automatically each visit    
 Register
Register		 Log in to check your private messages
Log in to check your private messages

  Visual Basic Forum for Visual Basic Programmers VB - Interactive Visual Basic Forum .NET Programming & Help
Visual Basic Forum for Visual Basic Programmers VB Forum Index » Database & Reporting

Post new topic   Reply to topic
sql statement
View previous topic :: View next topic  
Author Message
KaleKYlssen
Newbie


Joined: 31 Jan 2004
Posts: 19
PostPosted: Feb 3rd, 2004 01:29 PM    Post subject: sql statement Reply with quote
what would sql statement be if i wanted to return a record from a database that has a double key, for a single key record i have this

Code:

sqlstaff = "SELECT * FROM Staff WHERE StaffId = " & cboStaff.Text


but on my new form i want to open the record that has staffid and the weekstarting fields linked, the staffid will be selected from a combo box but the week will be from a textbox
Back to top
View user's profile Send private message Send e-mail
Avis
Junior Poster


Joined: 07 Oct 2003
Posts: 510
Location: India
PostPosted: Feb 3rd, 2004 01:39 PM    Post subject: Reply with quote
hello

Code:
sqlstaff = "SELECT * FROM Staff WHERE  StaffID = " & cboStaff.Text & " AND WHERE WeekStarting = '" & txtWeek.Text & "'"


i hope this helps!
_________________
Code Snippets, Tutorials, Utilities, Controls

Low cost Web Hosting
Hosting starts at as low as $4 per year!

Always follow posting guidelines
Put your VB code in [vb ] your code [ /vb] tags!
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger MSN Messenger ICQ Number
KaleKYlssen
Newbie


Joined: 31 Jan 2004
Posts: 19
PostPosted: Feb 3rd, 2004 01:54 PM    Post subject: Reply with quote
i've done that but now it says no value given for one or more required parameters and highlights rsweek.open line

Code:

Private Sub cboStaff_Click()
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
sqlefficiency = "SELECT * FROM Staff WHERE  StaffID = " & cboStaff.Text & " AND WeekStarting = '" & cboWeek.Text & "'"

rsweek.Open sqlefficiency, ConnString, adOpenKeyset, adLockOptimistic, adCmdText
rsweek.MoveFirst
txtMotor.Text = rsweek.Fields("TimeTaken") / rsweek.Fields("MotorsMade") * 60
rsweek.Close
Back to top
View user's profile Send private message Send e-mail
Avis
Junior Poster


Joined: 07 Oct 2003
Posts: 510
Location: India
PostPosted: Feb 3rd, 2004 02:01 PM    Post subject: Reply with quote
Hi!

Try this one:

Code:
sqlefficiency = "SELECT * FROM Staff WHERE  StaffID = " & cboStaff.Text & " AND WHERE WeekStarting = '" & cboWeek.Text & "'"


Thanks!
_________________
Code Snippets, Tutorials, Utilities, Controls

Low cost Web Hosting
Hosting starts at as low as $4 per year!

Always follow posting guidelines
Put your VB code in [vb ] your code [ /vb] tags!
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger MSN Messenger ICQ Number
KaleKYlssen
Newbie


Joined: 31 Jan 2004
Posts: 19
PostPosted: Feb 3rd, 2004 02:14 PM    Post subject: Reply with quote
still no look i'll post the entire code for that form if it will help

Code:

Dim rsstaff As ADODB.Recordset
Dim cn As ADODB.Connection
Dim rsweek As ADODB.Recordset



Private Sub cboStaff_Click()
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
sqlefficiency = "SELECT * FROM Efficiency WHERE  StaffID = " & cboStaff.Text & " AND WeekStarting = " & cboWeek.Text & ""

rsweek.Open sqlefficiency, ConnString, adOpenKeyset, adLockOptimistic, adCmdText
rsweek.MoveFirst
txtMotor.Text = rsweek.Fields("TimeTaken") / rsweek.Fields("MotorsMade") * 60
rsweek.Close
End Sub

Private Sub cmdExit_Click()
Unload Me
frmMenu.Show
End Sub


Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rsstaff = New ADODB.Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
rsstaff.Open "Staff", cn.ConnectionString, adOpenKeyset, adLockPessimistic, adCmdTable

rsstaff.MoveFirst
Do Until rsstaff.EOF = True
    cboStaff.AddItem rsstaff.Fields("StaffId")
        rsstaff.MoveNext
Loop
rsstaff.Close

Set rsweek = New ADODB.Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
rsweek.Open "Efficiency", cn.ConnectionString, adOpenKeyset, adLockPessimistic, adCmdTable

rsweek.MoveFirst
Do Until rsweek.EOF = True
    cboWeek.AddItem rsweek.Fields("WeekStarting")
        rsweek.MoveNext
Loop
rsweek.Close
End Sub
Back to top
View user's profile Send private message Send e-mail
Avis
Junior Poster


Joined: 07 Oct 2003
Posts: 510
Location: India
PostPosted: Feb 4th, 2004 10:54 AM    Post subject: Reply with quote
Hi!

Try this one:

Code:
Dim rsstaff As ADODB.Recordset
Dim cn As ADODB.Connection
Dim rsweek As ADODB.Recordset



Private Sub cboStaff_Click()
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
sqlefficiency = "SELECT * FROM Efficiency WHERE StaffID = " & cboStaff.Text & " AND WHERE WeekStarting = '" & cboWeek.Text & "'"

rsweek.Open sqlefficiency, ConnString, adOpenKeyset, adLockOptimistic, adCmdText
rsweek.MoveFirst
txtMotor.Text = rsweek.Fields("TimeTaken") / rsweek.Fields("MotorsMade") * 60
rsweek.Close
End Sub

Private Sub cmdExit_Click()
Unload Me
frmMenu.Show
End Sub


Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rsstaff = New ADODB.Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
rsstaff.Open "Staff", cn.ConnectionString, adOpenKeyset, adLockPessimistic, adCmdTable

rsstaff.MoveFirst
Do Until rsstaff.EOF = True
    cboStaff.AddItem rsstaff.Fields("StaffId")
        rsstaff.MoveNext
Loop
rsstaff.Close

Set rsweek = New ADODB.Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
rsweek.Open "Efficiency", cn.ConnectionString, adOpenKeyset, adLockPessimistic, adCmdTable

rsweek.MoveFirst
Do Until rsweek.EOF = True
    cboWeek.AddItem rsweek.Fields("WeekStarting")
        rsweek.MoveNext
Loop
rsweek.Close
End Sub

_________________
Code Snippets, Tutorials, Utilities, Controls

Low cost Web Hosting
Hosting starts at as low as $4 per year!

Always follow posting guidelines
Put your VB code in [vb ] your code [ /vb] tags!
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger MSN Messenger ICQ Number
KaleKYlssen
Newbie


Joined: 31 Jan 2004
Posts: 19
PostPosted: Feb 4th, 2004 01:24 PM    Post subject: Reply with quote
when i use that code it states a syntax error, but when i remove the WHERE from the sql statement that error doesn't occur but the no value error still happens. :huh:
Back to top
View user's profile Send private message Send e-mail
KaleKYlssen
Newbie


Joined: 31 Jan 2004
Posts: 19
PostPosted: Feb 4th, 2004 01:32 PM    Post subject: Reply with quote
heres the project so far so you can check the database for the error (theres bound to be some)
Back to top
View user's profile Send private message Send e-mail
Andir
Centurion


Joined: 21 Dec 2003
Posts: 184
Location: Chicago Area
PostPosted: Feb 4th, 2004 03:06 PM    Post subject: Reply with quote
One very important thing to not when working with SELECT statements is the type of data your pulling. I haven't pulled the data yet, but if your dealing with dates and times, surround your date with # signs:

.."WHERE someDate = #" & myDate & "#"

The same hold true with string values (char/varchar). Unless you explicitly say in the database that the variable type is a number(int/long/double), you should put a ' around the data:

.."WHERE someValue = '" & myValue & "'"

This might be the problem your running into as Avis was showing you with the ' signs he added.

Is the StaffID an AutoNumber field? or a "char" field? Is the WeekStarting a date?
_________________
If you happen to see little people sitting on your desk...don't tell anyone or they might think your crazy too.
Back to top
View user's profile Send private message
KaleKYlssen
Newbie


Joined: 31 Jan 2004
Posts: 19
PostPosted: Feb 5th, 2004 04:53 AM    Post subject: Reply with quote
The staffid field is text but the weekstarting is a date, i'm at college at the moment so will try your sugestions when i get home
Thanks
Back to top
View user's profile Send private message Send e-mail
KaleKYlssen
Newbie


Joined: 31 Jan 2004
Posts: 19
PostPosted: Feb 5th, 2004 02:09 PM    Post subject: Reply with quote
ok that worked thanks alot for your help
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    Visual Basic Forum for Visual Basic Programmers VB Forum Index » Database & Reporting All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Visual Basic Forum runs phpBB | Forum Template © iOptional
VB Resources | SSL | Visual Basic