ADO connect to SQL server

[Guest]

I have a table on the SQL sever that I would like to connect to it and search
for a fields that already there in the table. However, when i debug, the
program skip the rsttbl.MoveFirst and jump out of the loop. That means it can
not find that table's record. I think I declare correct connection as
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

Do you know why?
Thank you







Do you know why?

 

[Dirk Goldgar]

I have a table on the SQL sever that I would like to connect to it
and search for a fields that already there in the table. However,
when i debug, the program skip the rsttbl.MoveFirst and jump out of
the loop. That means it can not find that table's record. I think I
declare correct connection as oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

Do you know why?

In the actual connection string, did you replace "myServerName",
"myDatabaseName", "myUsername", and "myPassword" with the values that
are appropriate to your server and database?

 

[Guest]

Yes, I do. Of course!

In the actual connection string, did you replace "myServerName",
"myDatabaseName", "myUsername", and "myPassword" with the values that
are appropriate to your server and database?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Yes, I do. Of course!

Sorry, but one does have to ask these questions.

Is your SQL Server using a trusted connection, or do you have to provide
user ID and password when you connect to it?

 

[Tim Ferguson]

However, when i debug, the
program skip the rsttbl.MoveFirst and jump out of the loop.

What is the actual code you are using..?


Tim F

 

[Guest]

Sirs:
Right now I guess there is something to do with passing variables. Can I
grab a value from a current form and then pass it into a module and connect
to the database?
Thanks

 

[Dirk Goldgar]

Sirs:
Right now I guess there is something to do with passing variables.
Can I grab a value from a current form and then pass it into a module
and connect to the database?

Sure you can. But you'll have to answer Tim's question ...

.... if you want us to advise you.

 

[Guest]

Ok, I call a sub and passing an argument to it (i.e., studenNumber). This
value will be used to compare with the one in a table. The SQL statement is
call Searching(Me.StudentNumber)
In the Searching sub, the Me.StudentNumber (the current one that is showed
up on the form) is used to search to match with the one in a table. However,
it does not find it even though it is there in the table. Do you know why?

 

[Dirk Goldgar]

Ok, I call a sub and passing an argument to it (i.e., studenNumber).
This value will be used to compare with the one in a table. The SQL
statement is call Searching(Me.StudentNumber)
In the Searching sub, the Me.StudentNumber (the current one that is
showed up on the form) is used to search to match with the one in a
table. However, it does not find it even though it is there in the
table. Do you know why?

Again, it's impossible for anyone to say unless you post the code.

 

[Guest]

Here it is,
Call CheckOutStud(Me.StudNum) (Me.StudNum is the current StudNum shown on
the form)

Sub CheckOutStud(varStudNum As String)
Dim connectStr As String
connectStr = "PROVIDER=SQLOLEDB;Data Source=MyDS;InitialCatalog=MyDB; User
Id=;Password="
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open connectStr

Dim rstblStud As ADODB.Recordset
Set rstblStud = New ADODB.Recordset

strSql = "SELECT tblStud.StudNum FROM tblStud WHERE (tblStud.StudNum = ' " &
varStudNum & " ') ;"
rstblStud.Open strSql, cn

Do Until rstblStud.EOF
rstblStud.MoveFirst
'code in here
Loop
'code here

 

[Dirk Goldgar]

Here it is,
Call CheckOutStud(Me.StudNum) (Me.StudNum is the current StudNum
shown on the form)

Sub CheckOutStud(varStudNum As String)
Dim connectStr As String
connectStr = "PROVIDER=SQLOLEDB;Data Source=MyDS;InitialCatalog=MyDB;
User Id=;Password="
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open connectStr

Dim rstblStud As ADODB.Recordset
Set rstblStud = New ADODB.Recordset

strSql = "SELECT tblStud.StudNum FROM tblStud WHERE (tblStud.StudNum
= ' " & varStudNum & " ') ;"
rstblStud.Open strSql, cn

Do Until rstblStud.EOF
rstblStud.MoveFirst
'code in here
Loop
'code here

The student number is a text field? If not, you don't need the quotes
around the value in your SQL string. In that case, try this:

strSql = _
"SELECT tblStud.StudNum FROM tblStud " & _
"WHERE (tblStud.StudNum = " & varStudNum & ") ;"

Assuming the student number *is* a text field, you're embedding spaces
around the value, inside the quotes. In that case, try this:

strSql = _
"SELECT tblStud.StudNum FROM tblStud " & _
"WHERE (tblStud.StudNum = '" & varStudNum & '") ;"

Note that the WHERE line has single-quote, double-quote, & varStudNum &,
double-quote, single-quote.

 

[Guest]

Dirk, You are genius. Thank you for your help. The problem is the spaces
between the variable!!!

Thank you again. I appreciate it.