Modulate and Query...

[Pzykotik]

I got stuck again. Using VB.net I'm trying to develop an application with Windows CE 5.0 that access a database to get a load of information and display it. Why can't I put my connection string into a module? The connection string is going to change once I've finished the program and as I'm going to have to connect to, and close my connection to the database a lot I don't want to have to change hundreds of instances of the string when I should (in theory) just be able to change the module... right? So that's my first problem... Getting the open database function into a module.

My second is that I need to query what's been entered into a text box, against the database (the entered UserName against the fullName field in the tbleEmloyees table) I don't seem to be able to just call a SELECT statement to compare the data, so how do I do this?

This is what I have so far...

Imports System.Data.SqlServerCe

Public Class Drivers
    'Global Variables
    Dim userName As String
    Dim password As String

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        'save the entered username into a variable
        userName = tbUserName.Text
        'save the entered password into a variable
        password = tbPassword.Text

        Dim jitDatabase As New SqlCeConnection("Data Source = My Documents\JIT.sdf")
        jitDatabase.Open()

    End Sub
End Class

Again, thankyou ever so much!

Pzy

[Pzykotik]

Anybody?

[petevick]

HI,
I have a general routine, Get_Resultset_From_Database which is...

Public Function Get_ResultSet_From_Database(ByVal sSql As String, ByVal cnConn As SqlCeConnection) As SqlCeResultSet

        Dim sqlCmd As SqlCeCommand
        Try
            sqlCmd = New SqlCeCommand(sSql, cnConn)
            sqlCmd.CommandType = CommandType.Text
            Get_ResultSet_From_Database = sqlCmd.ExecuteResultSet(ResultSetOptions.Scrollable)
            iRows = CType(Get_ResultSet_From_Database.ResultSetView, ComponentModel.IBindingList).Count
        Catch Ex As SqlCeException
            DisplaySQLCEErrors(Ex, sSql, True)
            Get_ResultSet_From_Database = Nothing
            iRows = 0
        End Try

        sqlCmd.Dispose()
        sqlCmd = Nothing

    End Function

It is called by

  Dim sCustName As String = ""
        strSql = "Select custname from ssg_laundrycusttable where laundrycustid = '" & sCust & "'"
        Dim drGen As SqlCeResultSet = Get_ResultSet_From_Database(strSql, sqlCeConn)
        If iRows = 0 Then
            drGen.Close()
            drGen.Dispose()
            drGen = Nothing
            Return ""
        End If


        While (drGen.Read)
            sCustName = drGen("custname")
        End While
        drGen.Close()
        drGen.Dispose()
        drGen = Nothing()

So basically, formulate your sql, call it as above, and then drgen(fieldname) or drgen(index) contains your fields. Irows contains the number of rows found.

Does this help?

Pete

[Pzykotik]

That's fantastic! We're getting thrown out of the office now so I'll try it out tomorrow and let you know how I do. Thanks a lot

Alex

[Pzykotik]

Ok, wow this program changes a lot! So the newest task is to write a module that will access a database over GPRS

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

That should work right? run a query to download some tables, these tables will be selected using the username of the driver logged into the PDA... so something like

SELECT currentDrops FROM Routes WHERE driverName = userName;

and a few other SELECT statements... ie what they're dropping off and stuff

I need to download theses tables to a dataset, then close my connection to the server and close my GPRS connection, save the dataset to XML (which is just dataset.writeXML I think) and then re-open the XML as a dataset to work on it.

Anybody know how I go about this? haha,

Thanks!

[petevick]

Hi,
If that is the way you are going, I would take a long hard look at merge replication - it would save you re-inventing the wheel.

Pete

[Pzykotik]

there's a problem with that I think... I'm not connecting to a MS SQL Server. The network manager here uses something weird (mumps I think it's called), and I still can't get the damn thing to connect.

Would somebody be able to write me a module that connects to an IP address. It sounds really simple and I have NO IDEA why I can't figure it out. It would help me so much if I could just connect to the damn thing!