Cute Editor with VB.NET and SQL Server

Last post 11-22-2005, 12:31 PM by Adam. 2 replies.
Sort Posts: Previous Next
  •  11-21-2005, 1:16 PM 12838

    Cute Editor with VB.NET and SQL Server

    I am new to CE and am evaluating it for a project. I have been unable to find some sample code that uses CE in VB.NET specifically to read from and write to an  SQL Server database. I see from the samples that ship with CE that the column type in the table should be of type "image".
     
    Does anyone have some sample code that reads/writes to SQL Server database in VB.NET?
     
    If so, could you send it to [email protected] as I am not sure I could find my way back into this forum.
  •  11-22-2005, 2:43 AM 12847 in reply to 12838

    Re: Cute Editor with VB.NET and SQL Server

    Code was cut & pasted from one of my projects....so may not be 100% on its own.

    You will need to instantiate the class first, then call its functions.

    You can then access the data one record at a time through the public properties of the class.

    'Remember that the variables passed to sql in an insert statement must match the types that are  defined in the slq database.

    Imports System.Data.SqlClient
    Public Class updatedatabase

        'some public variables to place data into
        Public FirstName As String
        Public LastName As String
        Public TelephoneNumber As String
        Public Fax As String
        Public MobilePhone As String
        Public StreetAddress As String
        Public Suburb As String
        Public State As String
        Public PostalCode As String
        Public Country As String
        Public Company As String
        Public JobTitle As String
        Public InternetWebSight As String
        Public Description As String
        Public username As String
        Public password As String
        Public email As Strin
    Dim sqluser As String = "<sql user name goes here>"
    Dim pwd As String = "<sqlpassword goed here>"
    Dim strSQLServer As String = "<serverName Goes Here>"
    Dim databasename = "<DatabaseName goes here Name>"
    dim table = "visits"
    Dim sqlConnectionString = "DATA SOURCE=" & strSQLServer & ";USER ID=" & sqluser & ";PASSWORD=" & pwd & ";INITIAL CATALOG=" & databasename & ";"
    Dim sqlconn As SqlConnection = New SqlConnection(sqlConnectionString)

    'This function updates the fields "UserHostname", "Visitime", and "Page" in a table called "Visits", with to passed function 'paramaters "url" , and "host".
    '***************************************************************************************************
        Public Function updateit(ByVal url As String, ByVal host As String) As Boolean

            Dim sqlCommand As SqlCommand
            Dim insertCommand As String
            insertCommand = "insert into visits values (@UserHostName, @VisitTime, @Page)"
            sqlCommand = New SqlCommand(insertCommand, sqlconn)
            sqlCommand.Parameters.Add(New SqlParameter("@UserHostName", SqlDbType.VarChar, 100))
            sqlCommand.Parameters.Add(New SqlParameter("@VisitTime", SqlDbType.DateTime, 8))
            sqlCommand.Parameters.Add(New SqlParameter("@Page", SqlDbType.VarChar, 100))

            sqlCommand.Parameters("@UserHostName").Value = host
            sqlCommand.Parameters("@VisitTime").Value = Now
            sqlCommand.Parameters("@Page").Value = url
            sqlCommand.Connection.Open()
            Try
                sqlCommand.ExecuteNonQuery()
                updateit = True
            Catch ex As Exception
                updateit = False
            End Try
            sqlCommand.Connection.Close()
        End Function

    'This takes two passed paramaters "logonname" and "logonpassword" and searched a database (configured in the str variable
    '"databaseName") and querys the table "Visits" (configured with str variable "Table"). The data is bound through a dataset table "UserAccounts"
    'throught the sqlClient dataadapter. The data from this dataset is then placed into a set of appropriate public string variables.
    'The function returns true if the username and password were found in the database table. 

    function reads a database a table and uses the select command to return a record from the database.
     Public Function trylogon(ByVal logonName As String, ByVal logonpassword As String) As Boolean
         
            Dim sqlConnectionString = "DATA SOURCE=" & strSQLServer & ";USER ID=" & sqluser & _
            ";PASSWORD=" & pwd & ";INITIAL CATALOG=" & databaseName & ";"
            Dim sqlconn As SqlConnection = New SqlConnection(sqlConnectionString)
            Dim selectCommand As String
            selectCommand = "Select * from " & table & " where  username='" & logonName & _
            "' AND password='" & logonpassword & "'"
            Dim sqlda As SqlDataAdapter
            sqlda = New SqlDataAdapter(selectCommand, sqlconn)
            Dim sqldr As SqlDataReader
            Dim ds As System.Data.DataSet
            ds = New System.Data.DataSet
            sqlda.Fill(ds, "UserAccount")
            Try
                FirstName = ds.Tables("UserAccount").Rows(0).Item(0)
                LastName = ds.Tables("UserAccount").Rows(0).Item(1)
                TelephoneNumber = ds.Tables("UserAccount").Rows(0).Item(2)
                Fax = ds.Tables("UserAccount").Rows(0).Item(3)
                MobilePhone = ds.Tables("UserAccount").Rows(0).Item(4)
                StreetAddress = ds.Tables("UserAccount").Rows(0).Item(5)
                Suburb = ds.Tables("UserAccount").Rows(0).Item(6)
                State = ds.Tables("UserAccount").Rows(0).Item(7)
                PostalCode = ds.Tables("UserAccount").Rows(0).Item(8)
                Country = ds.Tables("UserAccount").Rows(0).Item(9)
                Company = ds.Tables("UserAccount").Rows(0).Item(10)
                JobTitle = ds.Tables("UserAccount").Rows(0).Item(11)
                InternetWebSight = ds.Tables("UserAccount").Rows(0).Item(12)
                username = ds.Tables("UserAccount").Rows(0).Item(13)
                Description = ds.Tables("UserAccount").Rows(0).Item(14)
                password = ds.Tables("UserAccount").Rows(0).Item(15)
                email = ds.Tables("UserAccount").Rows(0).Item(16)
                trylogon = True
               
            Catch ex As Exception
                trylogon = False
            End Try
            ds = Nothing
            sqlda = Nothing

        End Function

    End Class
  •  11-22-2005, 12:31 PM 12859 in reply to 12847

    Re: Cute Editor with VB.NET and SQL Server

View as RSS news feed in XML