|
 |
asp_databases thread: Getting a record set. Simple?!
Message #1 by "John" <jimihendrix@t...> on Wed, 9 Jan 2002 17:02:05 -0000
|
|
Can anyone PLEASE tell me why this doesn't work?
Many thanks.
---The form---
Private Sub Text1_Change()
Set o = CreateObject("Project1.Query")
Set oRS = o.GetCustomer(5)
Form1.Text1 = "There are " & oRS.RecordCount & "
records"
Set oRS = Nothing
End Sub
---/The form---
---The DLL---
Public Function GetCustomer(custnum) As ADODB.Recordset
'Returns the customer name from the customer number
Dim strWhereCustNum As String ' builds the WHERE
clause
Dim strSQL As String ' the SQL statement
Dim strConn As String ' the connection string
Dim CustName As String ' the customers name
Dim oRS As New ADODB.Recordset ' the ADO recordset
'Prepares the SQL statement
strSQL = "SELECT customertable.customersurname" & _
"FROM customertable" & _
"WHERE customernumber =" & custnum
'Prepares the connection string
strConn = "Provider=SQLOLEDB;uid=simes;pwd=ymww3a54;"
'Initial Catalog=CustomerTable"
'Runs the query
With oRS
.CursorLocation = adUseClient
.Open strSQL, strConn
End With
Set GetCustomer = oRS
'Disconnects RS
Set oRS.ActiveConnection = Nothing
End Function
---/The DLL---
Message #2 by "John Cardiff" <jimihendrix@t...> on Wed, 9 Jan 2002 17:23:40
|
|
Can anyone PLEASE tell me why this doesn't work?
Many thanks.
---The form---
Private Sub Text1_Change()
Set o = CreateObject("Project1.Query")
Set oRS = o.GetCustomer(5)
Form1.Text1 = "There are " & oRS.RecordCount & "
records"
Set oRS = Nothing
End Sub
---/The form---
---The DLL---
Public Function GetCustomer(custnum) As ADODB.Recordset
'Returns the customer name from the customer number
Dim strWhereCustNum As String ' builds the WHERE
clause
Dim strSQL As String ' the SQL statement
Dim strConn As String ' the connection string
Dim CustName As String ' the customers name
Dim oRS As New ADODB.Recordset ' the ADO recordset
'Prepares the SQL statement
strSQL = "SELECT customertable.customersurname" & _
"FROM customertable" & _
"WHERE customernumber =" & custnum
'Prepares the connection string
strConn = "Provider=SQLOLEDB;uid=simes;pwd=ymww3a54;"
'Initial Catalog=CustomerTable"
'Runs the query
With oRS
.CursorLocation = adUseClient
.Open strSQL, strConn
End With
Set GetCustomer = oRS
'Disconnects RS
Set oRS.ActiveConnection = Nothing
End Function
---/The DLL---
Message #3 by "Breidenbach, Beth" <Beth.Breidenbach@g...> on Wed, 9 Jan 2002 09:21:33 -0800
|
|
What error do you receive?
-----Original Message-----
From: John Cardiff [mailto:jimihendrix@t...]
Sent: Wednesday, January 09, 2002 9:24 AM
To: ASP Databases
Subject: [asp_databases] Getting a record set. Simple?!
Can anyone PLEASE tell me why this doesn't work?
Many thanks.
---The form---
Private Sub Text1_Change()
Set o =3D CreateObject("Project1.Query")
Set oRS =3D o.GetCustomer(5)
Form1.Text1 =3D "There are " & oRS.RecordCount & "
records"
Set oRS =3D Nothing
End Sub
---/The form---
---The DLL---
Public Function GetCustomer(custnum) As ADODB.Recordset
'Returns the customer name from the customer number
Dim strWhereCustNum As String ' builds the WHERE
clause
Dim strSQL As String ' the SQL statement
Dim strConn As String ' the connection string
Dim CustName As String ' the customers name
Dim oRS As New ADODB.Recordset ' the ADO recordset
'Prepares the SQL statement
strSQL =3D "SELECT customertable.customersurname" & _
"FROM customertable" & _
"WHERE customernumber =3D" & custnum
'Prepares the connection string
strConn =3D "Provider=3DSQLOLEDB;uid=3Dsimes;pwd=3Dymww3a54;"
'Initial Catalog=3DCustomerTable"
'Runs the query
With oRS
.CursorLocation =3D adUseClient
.Open strSQL, strConn
End With
Set GetCustomer =3D oRS
'Disconnects RS
Set oRS.ActiveConnection =3D Nothing
End Function
---/The DLL---
$subst('Email.Unsub').
Message #4 by "John Cardiff" <jimihendrix@t...> on Wed, 9 Jan 2002 18:58:10
|
|
> What error do you receive?
>
> -----Original Message-----
> From: John Cardiff [mailto:jimihendrix@t...]
> Sent: Wednesday, January 09, 2002 9:24 AM
> To: ASP Databases
> Subject: [asp_databases] Getting a record set. Simple?!
>
>
> Can anyone PLEASE tell me why this doesn't work?
> Many thanks.
>
>
> ---The form---
>
> Private Sub Text1_Change()
>
> Set o =3D CreateObject("Project1.Query")
> Set oRS =3D o.GetCustomer(5)
> Form1.Text1 =3D "There are " & oRS.RecordCount & "
> records"
> Set oRS =3D Nothing
>
> End Sub
>
> ---/The form---
>
>
> ---The DLL---
>
> Public Function GetCustomer(custnum) As ADODB.Recordset
>
> 'Returns the customer name from the customer number
>
> Dim strWhereCustNum As String ' builds the WHERE
> clause
> Dim strSQL As String ' the SQL statement
> Dim strConn As String ' the connection string
> Dim CustName As String ' the customers name
> Dim oRS As New ADODB.Recordset ' the ADO recordset
>
> 'Prepares the SQL statement
>
> strSQL =3D "SELECT customertable.customersurname" & _
> "FROM customertable" & _
> "WHERE customernumber =3D" & custnum
>
> 'Prepares the connection string
>
> strConn =3D "Provider=3DSQLOLEDB;uid=3Dsimes;pwd=3Dymww3a54;"
> 'Initial Catalog=3DCustomerTable"
>
> 'Runs the query
>
> With oRS
> .CursorLocation =3D adUseClient
> .Open strSQL, strConn
> End With
> Set GetCustomer =3D oRS
>
> 'Disconnects RS
>
> Set oRS.ActiveConnection =3D Nothing
>
> End Function
>
> ---/The DLL---
>
> $subst('Email.Unsub').
Message #5 by "John Cardiff" <jimihendrix@t...> on Wed, 9 Jan 2002 19:01:05
|
|
I have set it all up on my machine and the connections are all working.
The error I get is:
Run-time error '-2147217900 (80040e14)':
Line 1:Incorrect syntax near 'customernumber'
Message #6 by Brooks_Piggott@D... on Wed, 9 Jan 2002 13:15:34 -0600
|
|
This reason can be found if you display the SQL Query before it processes.
> strSQL =3D "SELECT customertable.customersurname" & _
> "FROM customertable" & _
> "WHERE customernumber =3D" & custnum
This will evaluate to SELECT customertable.customersurnameFROM customertable
So it's looking for the table named customersurnameFrom
If you add a space between the tablename and the word FROM (and the same
thing will happen on the WHERE) then you should be fine.
I do it 99% of the time I write a sql statement.
Brooks Piggott
-----Original Message-----
From: John Cardiff [mailto:jimihendrix@t...]
Sent: Wednesday, January 09, 2002 12:58 PM
To: ASP Databases
Subject: [asp_databases] RE: Getting a record set. Simple?!
> What error do you receive?
>
> -----Original Message-----
> From: John Cardiff [mailto:jimihendrix@t...]
> Sent: Wednesday, January 09, 2002 9:24 AM
> To: ASP Databases
> Subject: [asp_databases] Getting a record set. Simple?!
>
>
> Can anyone PLEASE tell me why this doesn't work?
> Many thanks.
>
>
> ---The form---
>
> Private Sub Text1_Change()
>
> Set o =3D CreateObject("Project1.Query")
> Set oRS =3D o.GetCustomer(5)
> Form1.Text1 =3D "There are " & oRS.RecordCount & "
> records"
> Set oRS =3D Nothing
>
> End Sub
>
> ---/The form---
>
>
> ---The DLL---
>
> Public Function GetCustomer(custnum) As ADODB.Recordset
>
> 'Returns the customer name from the customer number
>
> Dim strWhereCustNum As String ' builds the WHERE
> clause
> Dim strSQL As String ' the SQL statement
> Dim strConn As String ' the connection string
> Dim CustName As String ' the customers name
> Dim oRS As New ADODB.Recordset ' the ADO recordset
>
> 'Prepares the SQL statement
>
> strSQL =3D "SELECT customertable.customersurname" & _
> "FROM customertable" & _
> "WHERE customernumber =3D" & custnum
>
> 'Prepares the connection string
>
> strConn =3D "Provider=3DSQLOLEDB;uid=3Dsimes;pwd=3Dymww3a54;"
> 'Initial Catalog=3DCustomerTable"
>
> 'Runs the query
>
> With oRS
> .CursorLocation =3D adUseClient
> .Open strSQL, strConn
> End With
> Set GetCustomer =3D oRS
>
> 'Disconnects RS
>
> Set oRS.ActiveConnection =3D Nothing
>
> End Function
>
> ---/The DLL---
>
> $subst('Email.Unsub').
$subst('Email.Unsub').
Message #7 by "John Cardiff" <jimihendrix@t...> on Wed, 9 Jan 2002 20:12:33
|
|
Thanks Brooks. Silly mistake!
-------
> This reason can be found if you display the SQL Query before it
processes.
>
> > strSQL =3D "SELECT customertable.customersurname" & _
> > "FROM customertable" & _
> > "WHERE customernumber =3D" & custnum
>
> This will evaluate to SELECT customertable.customersurnameFROM
customertable
>
> So it's looking for the table named customersurnameFrom
>
> If you add a space between the tablename and the word FROM (and the same
> thing will happen on the WHERE) then you should be fine.
>
> I do it 99% of the time I write a sql statement.
>
> Brooks Piggott
>
Message #8 by "Drew, Ron" <RDrew@B...> on Wed, 9 Jan 2002 17:27:47 -0500
|
|
In the example below it would error because there is no spaces between
parameters and key words.
strSQL =3D3D "SELECT customertable.customersurname" & _
" FROM customertable" & _
" WHERE customernumber =3D3D" & custnum
-----Original Message-----
From: Brooks_Piggott@D... [mailto:Brooks_Piggott@D...]
Sent: Wednesday, January 09, 2002 2:16 PM
To: ASP Databases
Subject: [asp_databases] RE: Getting a record set. Simple?!
This reason can be found if you display the SQL Query before it
processes.
> strSQL =3D3D "SELECT customertable.customersurname" & _
> "FROM customertable" & _
> "WHERE customernumber =3D3D" & custnum
This will evaluate to SELECT customertable.customersurnameFROM
customertable
So it's looking for the table named customersurnameFrom
If you add a space between the tablename and the word FROM (and the same
thing will happen on the WHERE) then you should be fine.
I do it 99% of the time I write a sql statement.
Brooks Piggott
-----Original Message-----
From: John Cardiff [mailto:jimihendrix@t...]
Sent: Wednesday, January 09, 2002 12:58 PM
To: ASP Databases
Subject: [asp_databases] RE: Getting a record set. Simple?!
> What error do you receive?
>
> -----Original Message-----
> From: John Cardiff [mailto:jimihendrix@t...]
> Sent: Wednesday, January 09, 2002 9:24 AM
> To: ASP Databases
> Subject: [asp_databases] Getting a record set. Simple?!
>
>
> Can anyone PLEASE tell me why this doesn't work?
> Many thanks.
>
>
> ---The form---
>
> Private Sub Text1_Change()
>
> Set o =3D3D CreateObject("Project1.Query")
> Set oRS =3D3D o.GetCustomer(5)
> Form1.Text1 =3D3D "There are " & oRS.RecordCount & " records"
> Set oRS =3D3D Nothing
>
> End Sub
>
> ---/The form---
>
>
> ---The DLL---
>
> Public Function GetCustomer(custnum) As ADODB.Recordset
>
> 'Returns the customer name from the customer number
>
> Dim strWhereCustNum As String ' builds the WHERE
> clause
> Dim strSQL As String ' the SQL statement
> Dim strConn As String ' the connection string
> Dim CustName As String ' the customers name
> Dim oRS As New ADODB.Recordset ' the ADO recordset
>
> 'Prepares the SQL statement
>
> strSQL =3D3D "SELECT customertable.customersurname" & _
> "FROM customertable" & _
> "WHERE customernumber =3D3D" & custnum
>
> 'Prepares the connection string
>
> strConn =3D3D
"Provider=3D3DSQLOLEDB;uid=3D3Dsimes;pwd=3D3Dymww3a54;"
> 'Initial Catalog=3D3DCustomerTable"
>
> 'Runs the query
>
> With oRS
> .CursorLocation =3D3D adUseClient
> .Open strSQL, strConn
> End With
> Set GetCustomer =3D3D oRS
>
> 'Disconnects RS
>
> Set oRS.ActiveConnection =3D3D Nothing
>
> End Function
>
> ---/The DLL---
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or=3D20 to
> unsubscribe send a blank email to
> $subst('Email.Unsub').
$subst('Email.Unsub').
$subst('Email.Unsub').
|
|
|
View Cart
