can send data to sql 2005, can not read data from sql 2005 with vb

21 Dec 2006 10:28 PM

Andrew

This is my first time retrieving data form a sql server 2005.

I can send data to sql 2005, but I am having problems retrieving data to
display to user from sql 2005.

Here is my code, can anybody help?

'connection string
            Dim conn As New ADODB.Connection
            Dim cmd As New ADODB.Command
            Dim rs As ADODB.Recordset
            Dim sConnString As String
            sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=" &
sPassword & ";Initial Catalog=" & sDBName & ";Data Source = " & sServer &
";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
            conn.Open(sConnString)

            'record set
            cmd.ActiveConnection = conn
            cmd.CommandText = "SELECT sysID, sysName FROM sysMaster WHERE
sysName = '" & sComputerName & "'"
            cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
            rs = cmd.Execute

            If rs.EOF Then
                'if not found in system
                Call addComputer()
            Else
                'get id
                'sID = rs.Fields("sysID")
                Call AddEnventory(rs.Fields("sysID"))
                Call AddDrives(rs.Fields("sysID"))
            End If

            rs.Close()
            conn.Close()

this code works for SQL 2000

22 Dec 2006 5:35 AM

Cor Ligthert [MVP]

Andrew,

Why do you not try .Net code in .Net. Adonet is much simpler than classic
Ado.
Beside that there are more and more less people who can give good answers
about classic Ado.

Cor

Show quoteHide quote

"Andrew" <And***@discussions.microsoft.com> schreef in bericht
news:05D250D7-D6E7-487F-9F2A-B51943324239@microsoft.com...
> This is my first time retrieving data form a sql server 2005.
>
> I can send data to sql 2005, but I am having problems retrieving data to
> display to user from sql 2005.
>
> Here is my code, can anybody help?
>
> 'connection string
>            Dim conn As New ADODB.Connection
>            Dim cmd As New ADODB.Command
>            Dim rs As ADODB.Recordset
>            Dim sConnString As String
>            sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=" &
> sPassword & ";Initial Catalog=" & sDBName & ";Data Source = " & sServer &
> ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
>            conn.Open(sConnString)
>
>            'record set
>            cmd.ActiveConnection = conn
>            cmd.CommandText = "SELECT sysID, sysName FROM sysMaster WHERE
> sysName = '" & sComputerName & "'"
>            cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
>            rs = cmd.Execute
>
>            If rs.EOF Then
>                'if not found in system
>                Call addComputer()
>            Else
>                'get id
>                'sID = rs.Fields("sysID")
>                Call AddEnventory(rs.Fields("sysID"))
>                Call AddDrives(rs.Fields("sysID"))
>            End If
>
>            rs.Close()
>            conn.Close()
>
> this code works for SQL 2000
>
>

22 Dec 2006 3:11 PM

Andrew

can you give me a sample of the .net way or point me to a sit that can?

Show quoteHide quote

"Cor Ligthert [MVP]" wrote:

> Andrew,
>
> Why do you not try .Net code in .Net. Adonet is much simpler than classic
> Ado.
> Beside that there are more and more less people who can give good answers
> about classic Ado.
>
> Cor
>
> "Andrew" <And***@discussions.microsoft.com> schreef in bericht
> news:05D250D7-D6E7-487F-9F2A-B51943324239@microsoft.com...
> > This is my first time retrieving data form a sql server 2005.
> >
> > I can send data to sql 2005, but I am having problems retrieving data to
> > display to user from sql 2005.
> >
> > Here is my code, can anybody help?
> >
> > 'connection string
> >            Dim conn As New ADODB.Connection
> >            Dim cmd As New ADODB.Command
> >            Dim rs As ADODB.Recordset
> >            Dim sConnString As String
> >            sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=" &
> > sPassword & ";Initial Catalog=" & sDBName & ";Data Source = " & sServer &
> > ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
> >            conn.Open(sConnString)
> >
> >            'record set
> >            cmd.ActiveConnection = conn
> >            cmd.CommandText = "SELECT sysID, sysName FROM sysMaster WHERE
> > sysName = '" & sComputerName & "'"
> >            cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
> >            rs = cmd.Execute
> >
> >            If rs.EOF Then
> >                'if not found in system
> >                Call addComputer()
> >            Else
> >                'get id
> >                'sID = rs.Fields("sysID")
> >                Call AddEnventory(rs.Fields("sysID"))
> >                Call AddDrives(rs.Fields("sysID"))
> >            End If
> >
> >            rs.Close()
> >            conn.Close()
> >
> > this code works for SQL 2000
> >
> >
>
>
>

22 Dec 2006 7:54 PM

Cor Ligthert [MVP]

Andrew,

I don't know if it is to complete but it has at least a lot in it.

http://www.vb-tips.com/dbpages.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

Cor

Show quoteHide quote

"Andrew" <And***@discussions.microsoft.com> schreef in bericht
news:7D2BE0C1-D119-4567-8AC5-52E2F681D3C3@microsoft.com...
> can you give me a sample of the .net way or point me to a sit that can?
>
> "Cor Ligthert [MVP]" wrote:
>
>> Andrew,
>>
>> Why do you not try .Net code in .Net. Adonet is much simpler than classic
>> Ado.
>> Beside that there are more and more less people who can give good answers
>> about classic Ado.
>>
>> Cor
>>
>> "Andrew" <And***@discussions.microsoft.com> schreef in bericht
>> news:05D250D7-D6E7-487F-9F2A-B51943324239@microsoft.com...
>> > This is my first time retrieving data form a sql server 2005.
>> >
>> > I can send data to sql 2005, but I am having problems retrieving data
>> > to
>> > display to user from sql 2005.
>> >
>> > Here is my code, can anybody help?
>> >
>> > 'connection string
>> >            Dim conn As New ADODB.Connection
>> >            Dim cmd As New ADODB.Command
>> >            Dim rs As ADODB.Recordset
>> >            Dim sConnString As String
>> >            sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=" &
>> > sPassword & ";Initial Catalog=" & sDBName & ";Data Source = " & sServer
>> > &
>> > ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
>> >            conn.Open(sConnString)
>> >
>> >            'record set
>> >            cmd.ActiveConnection = conn
>> >            cmd.CommandText = "SELECT sysID, sysName FROM sysMaster
>> > WHERE
>> > sysName = '" & sComputerName & "'"
>> >            cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
>> >            rs = cmd.Execute
>> >
>> >            If rs.EOF Then
>> >                'if not found in system
>> >                Call addComputer()
>> >            Else
>> >                'get id
>> >                'sID = rs.Fields("sysID")
>> >                Call AddEnventory(rs.Fields("sysID"))
>> >                Call AddDrives(rs.Fields("sysID"))
>> >            End If
>> >
>> >            rs.Close()
>> >            conn.Close()
>> >
>> > this code works for SQL 2000
>> >
>> >
>>
>>
>>