2008 problem with sql db

[smelf1]

Hi all,

I am just trying to get the information i collect from the pc's hostname and pass it into a db. All the db passwords, location etc are correct but i am getting nothing.

Imports System
Imports Microsoft.Win32
Imports System.Data
Imports System.Data.SqlClient


Public Class Form1
   
    Private Sub DB()

        'Your log file reader code goes here
        'The code below will insert one text value into one record.  Make sure there are no unique ID's etc that need to be added as well
        'If there are then these will have to be added also
        Dim myhostnameDescription As String = My.Computer.Registry.GetValue("HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\", "Hostname", "No Data Found").ToString.Trim
        Dim sqlConn As SqlConnection
        Dim sqlCmd As SqlCommand
        Dim myConnString As String
        Dim rowsAffected As Integer
        
        Try
            myConnString = "user id=user;data source=laptop34;persist security info=True;initial catalog=ClientAuditData;password=passw0rD"

            ' Create a new connection object
            sqlConn = New SqlConnection(myConnString)
            ' Create a new command object
            sqlCmd = New SqlCommand

            ' Specify the SQL expression and connection
            With sqlCmd
                .CommandType = CommandType.Text
                .Connection = sqlConn
            End With
            ' Open the connection
            sqlConn.Open()
            ' Execute the command. This returns rows affected.

            'insert record
            sqlCmd.CommandText = "insert into SystemData (SystemName) Values(" & myhostnameDescription & ");"
            rowsAffected = sqlCmd.ExecuteNonQuery()


        Catch exc As SqlException
            ' Handle the exception Â…
            MsgBox(Err.Description)
        Finally
            ' Close connection regardless of outcome
            sqlConn = Nothing
            sqlConn.Close()
        End Try

    End Sub


End Class

[Teme64]

There's at least one syntax error in the SQL: sqlCmd.CommandText = "insert into SystemData (SystemName) Values(" & myhostnameDescription & ");" should be sqlCmd.CommandText = "insert into SystemData (SystemName) Values('" & myhostnameDescription & "');" i.e. a string has to have single quotes around it.

[smelf1]

changed the string to have quotes but still nothing.

I have attached a pick of the connected database and sql server and now i get the attached error.

I have changed the code to point to the sql server on wks45 now and the username etc are correct.

[Teme64]

Ok, that might be more SQL Server configuration/firewall related problem.

Which SQL Server version do you use?

Anyway, here's something to check with 2005 version (assuming you can use TCP/IP instead of Named Pipes):
- start SQL Server 2005 Network Configuration Manager
- check that the TCP/IP is Enabled in the Protocols
- check from the TCP/IP Properties that it is using Port 1433 (this is the default, but you may use some other port number)
- check from the computer's (where the SQL Server is) firewall settings that it allows traffic through the port 1433 (or, if you use some other port, the port number you use). You may need to add an exception to the firewall to allow traffic through the port 1433 (or whatever port you use)

If you use SQL Server 2000, look for Client Network Utility to check protocol and port settings. I haven't yet used SQL Server 2008 but my guess is that it's called Network Configuration Manager there.