Accessing SQL 7.0 Database From VB

[razzaj]

What should I reference and how can I connect to an SQL 7 Database through VB ?? any help is greatly apreciated

[barrk]

We add an ADO control to our user control and display our data in datagrids, textboxes, listboxes etc. by setting the datasource of the control = adodc1 and adding the data using adodc1.recordset.fields("mydata"). The connection string, recordsource etc. are all handled by the ADO control. If we want to change the recordsource we do something like adodc1.recordsource = "select * from mytable"
adodc1.refresh

If you need a clearer explanation let me know. I'll send you a code snippet.

[Stockton.S]

The problem is that I have what could be termed no knowledge about this at all so any help would be appreciated.


I have a data combo that I have populated with the item field from my database and I want to be able to click on the relevent item and for the data corrisponding to that data to be displayed.

AS I said I have no knowledge about any of this so please help me.

Simon

[Samuel]

Hi,
try this:
dim connsql as adodb.connection
Set ConnSql = New ADODB.Connection
ConnSql.ConnectionTimeout = 25
ConnSql.Provider = "sqloledb"
ConnSql.Properties("Data Source").Value=txtServer.Text
ConnSql.Properties("Initial Catalog").Value = txtDBNome.Text

If optionbutton.Value = True Then
ConnSql.Properties("Integrated Security").Value= "SSPI"
Else
ConnSql.Properties("User ID").Value =txtUser.Text
ConnSql.Properties("Password").Value = txtPassword.Text
End If
ConnSql.Open
.
.
.
conn.close
set conn = nothing

[razzaj]

Thank you Samuel That is exactly what I needed

[twitchibob]

I am trying to connect as well, I was wondering if you could write the code for me with the following params:

ODBC DSN:IISLOG
SQL server Name: SQLSERVER
User: administrator
password: beer
Database:Logging
Table:InternetLog

I know this sounds horrible, but I am really new and your patience is really appreciated. Thanks in advance. I tried the code for Samuel, and put what I thought should go where and it puked out and said it couldn't find the driver or DSN.

Twitch

[Edited by twitchibob on 10-06-2000 at 10:01 AM]

[JSimon]

Try this - -

Set db = OpenDatabase("", False, False, "ODBC;DSN=IISLog;UID=administrator;PWD=beer;DATABASE=Logging;")

Make sure that your DSN is already created and set up properly.Then reference the Table in a Sql statement:
"SELECT * From InternetLog;" and set that equal to a recordset.

[barrk]

If you use an ADODC you don't have to set up a DSN on each client machine. If you have mutilple users it simplifies things enormously!

[airbaal]

I was reading through the various posts and am doing something similar to what Barrk said to do but I can't seem to get it working (I hate being a newbie at something...). Any chance you could send me a code snippet or post it here?

Thanks in advance.

-Eric
[email protected]

[barrk]

Are you adding an adodc control to a form and building your connection from there? If so, add the control and right click. Go to properties and add your connection string. It should look something like this:

Under the general tab, click "Use Connection String" and enter the following:
driver=[sql server];server=myserver;database=mydb;uid=myuser;pwd=;mypass; (uid and pwd are optional if you are using NT authorization in that case you would put uid=;pwd=

Under recordsource enter your sql statement "select * from sometable" ( you can change the recordsource thru code later and write adodc1.recordsource = mysqlstring then adodc1.refresh to display the new data.)

Does this help or make any sense at all????

[barrk]

You can also try the following if you don't want to add the adodc control...a little more difficult to code but it works just as well.

set objconn = createObject("ADODB.Connection")
objconn.open "driver={sql server};server=SQLSERVER;database=Logging;uid=administrator;Pwd=beer"
set mycommand = CreateObject("ADODB.Command")
Set mycommand.ActiveConnection = objconn
Set myrs = CreateObject("ADODB.Recordset")
mycommand.commandtext = "Select * from sometable"
myrs.open mycommand



Good Luck!!!

[airbaal]

Got it.

Thanks a lot :-)

-Eric

[airbaal]

One more quick question on the subject...

I want to have a paramter that says select certain records (such as invoices that are numbered 100).

If I do it from the Adodc1 > Properties > RecordSource with a SQL statement like:
Select * FROM tblInvoices
ORDER BY Invoice_Num, Part
WHERE invoice_num = ?

I get an error because the ? is not defined. But I can't figure out where to put my parameter information and how exactly it should be coded. Here is what I was thinking would work for the Parameter:

Dim objprm As New ADODB.Parameter
'
With objprm
.Name = "invoice_num"
.Type = adVarChar
.Direction = adParamInputOutput
.Size = 40
.Value = txtInvoiceNumberWanted.Text
End With

Ultimately I would like to have a command button and a text field where the user types in the invoice # and then hits a load button, which would run a SQL statement like the one I wrote above and would update the recordset to just have the new records.

Am I overlooking something obvious?

Thanks,

Eric

[JSimon]

First of all the order by needs to be last and not before the WHERE Clause!

If your are doing this in code set this to a string variable
If your Invoice Number is defined as a number then use the following code

Example:

sql1 = "Select * FROM tblInvoices WHERE invoice_num = & textbox.text & "ORDER BY Invoice_Num, Part"

Then just reference the sql1 and whatever the user entered in the Textbox.text will be what the sql server pulls forward.

Hope this helps!

[JSimon]

Sorry I forgot to add a quote here,Try this syntax instead:

sql1 = "Select * FROM tblInvoices WHERE invoice_num = " & textbox.text & "ORDER BY Invoice_Num, Part"

[airbaal]

Thanks...

I got it to work... I had to make the invoice_num field a numeric field and not a text field, otherwise it would not work. Is there any way it would work with a text field?

I used this for the numeric field:
Private Sub cmdLoadInvoice_Click()
'
Adodc1.RecordSource = "Select * FROM tblInvoices WHERE invoice_num = " & txtLoadInvoice.Text
Adodc1.Refresh
'
End Sub

The minute I changed it the invoice_num to a text field in the .mdb file it gives an error "Data type mismatch in criteria expression."

Is there any way I can use a text field, have numbers in it, and have it find the records I want?

Thanks again....

-Eric