[RESOLVED] VBScript for Database Commands

[TURKINATOR]

Hello,

I am building an application and would like to use VBScript to do all the INSERT, SELECT, DELETE ect.

I am kind of stuck in how to attempt such a thing. I have done some code for it but don't know how to transfer the data from the VBScript to the MainForm and then populate the data in the correct fields.

There is doing it using DataAdapter, but this will be doing many SQL, so I will be using DataReader for all of the SQL statements.

I am open to suggestions.

Main Form

Public Class frmMain

    Dim dbCommands As New dbCommands

    Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
        Dim tableValue As String = ""

        If radCaseFile.Checked = True Then
            tableValue = "CaseFile"
        Else
            tableValue = "AWF"
        End If

        dbCommands.dbOpen()
        dbCommands.dbSearch(tableValue, txtCaseID.Text, txtFName.Text, txtSName.Text)
        dbCommands.dbClose()
    End Sub
End Class

VBScript Commands

Public Class dbCommands

    Dim dbConnection As New MySql.Data.MySqlClient.MySqlConnection
    Dim dbCommand As New MySql.Data.MySqlClient.MySqlCommand
    Dim dbReader As MySql.Data.MySqlClient.MySqlDataReader

    Public Sub dbOpen()
        dbConnection.ConnectionString = "Server='XXX'; Database=XXX'; Port=XXX;" & _
        "Uid='XXX; Password='XXX;"
        dbConnection.Open()
    End Sub

    Public Sub dbClose()
        dbConnection.Close()
    End Sub

    Public Function dbSearch(ByVal table As String, ByVal caseid As String, ByVal fname As String, ByVal sname As String)
        Dim strSQL As String = ""
        Dim dataTable As New Data.DataTable

        strSQL = "SELECT FirstName, Surname," & table & ".CaseID FROM " & table & ", Subjects WHERE " & _
        "Subjects.CaseID = " & table & ".CaseID "

        If Not caseid = "" And fname = "" And sname = "" Then
            If Not caseid = "" Then
                strSQL += "AND CaseID = '" & caseid & "'"
            End If

            If Not fname = "" Then
                strSQL += "AND FirstName = '" & fname & "'"
            End If

            If Not sname = "" Then
                strSQL += "AND Surname = '" & sname & "'"
            End If

            dbCommand.CommandText = strSQL
            dbCommand.CommandType = CommandType.Text
            dbReader = dbCommand.ExecuteReader()

        End If

        Return ??????

    End Function

End Class

[santuvssantu]

Hi,
Why don't you return the entire SQLDataReader as object and populate the fields?

[TURKINATOR]

Hi,

Thanks for your reply. I figured it would be easier to use the DataAdapter for the single values and the DataReader for arrays. Aint the most kindest way, but it works I guess.

I will post my results later once I have completed enough as an example for someone else who may have the same query/issue.

[TURKINATOR]

Ok, Im half way through my application. Here is what I am doing.


Here is the class.

Public Class dbCommands

Public Function dbSearch(ByVal caseid As String)
        Dim strSQL As String = ""
        Dim dataTable As New Data.DataTable

        strSQL = "SELECT CaseFile.CaseID, FirstName, Surname, Phone, Email, Address, DOB, CurrStatus, DateOpen, " & _
        "State, PostCode, Mobile, ReferredBy, CaseFile.ProblemCode, Description, DateClose, ReceivingCountry, " & _
        "SendingCountry FROM CaseFile " & _
        "LEFT JOIN Subjects ON CaseFile.CaseID = Subjects.CaseID " & _
        "LEFT JOIN CaseHistory ON CaseFile.CaseID = CaseHistory.CaseID " & _
        "LEFT JOIN ProblemCodes ON CaseFile.ProblemCode = ProblemCodes.ProblemCode " & _
        "WHERE CaseFile.CaseID = '" & caseid & "' "

        dbCommand.CommandText = strSQL
        dbCommand.CommandType = CommandType.Text
        dbCommand.Connection = dbConnection
        dbOpen()
        dbReader = dbCommand.ExecuteReader
        dataTable.Load(dbReader)
        dbClose()

        Return dataTable

        dataTable.Dispose()

    End Function

..........

Here is where I call it and how I use it.

Public Class frmCaseFile

    Dim dbCommands As New dbCommands

    Private Sub frmCaseFile_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dataTable As Data.DataTable


        If Not txtCaseID.Text = "" Then
            dataTable = dbCommands.dbGetCaseFile(txtCaseID.Text)

            txtCaseID.DataBindings.Add(New Binding("TEXT", dataTable, "CaseID"))
            cboProblemCode.DataBindings.Add(New Binding("TEXT", dataTable, "Description"))
            cboSendCountry.DataBindings.Add(New Binding("TEXT", dataTable, "SendingCountry"))
            cboSendCountry.SelectedValue = cboSendCountry.Text
            cboRecCountry.DataBindings.Add(New Binding("TEXT", dataTable, "ReceivingCountry"))
            cboRecCountry.SelectedValue = cboRecCountry.Text

            dataTable.Dispose()
        End If

    End Sub

Also, thank jmcilhinney for a different post which gave me this direction.

http://www.vbforums.com/showthread.php?t=514210