Modulate and Query...

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...

Code:

---

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

Anybody?

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

Code:

---

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

Code:

---

  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

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

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

Code:

---

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

Code:

---

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!

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

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!