how to set a menuItem text properties to a value retrieved from a DB

Hi! I am designing an application that will be in french and english. So I will design two table with all the strings I need, on table in french and another in english. Like tbl_french_string and tbl_english_string, with field like STRING_MAIN_MENU_FILE = &File (&Fichier in french). At the application startup, I want to check the language selected and then loaded the right string. Like at startup, if English is selected, then it will loaded the tbl_english_string table and replace all the text with the right string. So there would be som,ething like that, mainMenu1.text = STRING_MAIN_MENU_FILE from the table loaded previously.

The problem I have is, that I know how to bind data to textboxes, but I dont know how to refer to dataset in the code ..., I dont know either how to create a dataset in the code, because I dont want to have two dataset with the two table, since only just one will be used, depending on what language has been selected. So I'd like to create the dataset at runtime to use the less memory. can anyone help me with this one!??

I hope I made myself clear, I'll check around, so posted any question and I will answer you if I'm not enogh clear. Thanks in advance!

Jean Christophe Avard

Here is an example of creating a dataset in code and filling it with sql.

VB Code:

Dim dsMnuCaptions As New DataSet
        Dim dtCaptions As New DataTable("Captions")
        Dim sqlDA As New SqlClient.SqlDataAdapter
        Dim sqlCmd As New SqlClient.SqlCommand
 
        sqlCmd.Connection = sqlConn 'here place whatever connection you are using
        sqlDA.SelectCommand = sqlCmd
        dsMnuCaptions.Tables.Add(dtCaptions)
 
        'Put whatever select command you need
        sqlCmd.CommandText = "SELECT STRING_MAIN_MENU_FILE FROM CaptionsTable WHERE Language=1"
        sqlDA.Fill(dsMnuCaptions.Tables("Captions"))
 
        'Use the dataset with whatever columns you selected in the sql query
        MnuItm.Text = dsMnuCaptions.Tables("Captions").Rows(0).Item("SELECT STRING_MAIN_MENU_FILE")

Hope that points you in the right direction.

Thanks a lot dude! but what would be the connection line... I'm using an Access database, oleDbConnection, I'm totally lost with that... it says the connection string asnt been initialized, but when I write: oleCmd.Connection ="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\InformIT\New\BindControls\DesignTime\Books.mdb"

it says It cant be a string...

this is my code now, does it make sense at all?

VB Code:

Private Sub frmMDI_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dsMnuCaptions As New System.Data.DataSet
        Dim dtCaptions As New System.data.DataTable("tbl_french_string")
        Dim oleDA As New System.data.OleDb.OleDbDataAdapter
        Dim oleCmd As New System.Data.OleDb.OleDbCommand
        Dim oleConn As New System.Data.OleDb.OleDbConnection
 
        'here place whatever connection you are using
        oleCmd.Connection = oleConn
        oleDA.SelectCommand = oleCmd
        dsMnuCaptions.Tables.Add(dtCaptions)
 
        'Put whatever select command you need
        oleCmd.CommandText = "SELECT STRING_MAIN_MENU_FILE FROM tbl_french_string"
        oleDA.Fill(dsMnuCaptions.Tables("tbl_french_string"))
 
        'Use the dataset with whatever columns you selected in the sql query
        'Dim menuitem As New System.Windows.Forms.MenuItem
 
        Me.MenuItem1.Text = dsMnuCaptions.Tables("tbl_french_string").Rows(0).Item("SELECT STRING_MAIN_MENU_FILE")
 
    End Sub

I gues the "Captions" thing would be the table I query?

"Captions" can be any word you want. Its just a notation to call the table by name.

for the connection try this.

VB Code:

oleConn.ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\InformIT\New\BindControls\DesignTime\Books.mdb"
 
oleDA.Connection = oleConn

If you are using a data adapter I don't think you have to associate the connection with the command, just associate with the adapter that is using the command.

Also, if you are still having problems with the connection string, try this -

In Visual Studio, right-click in Server Explorer and click on 'Add Connection'. Use the form that pops up to create the connection. Then once it is created & working, you can click on the connection and look at the properties window. It will have a property called 'ConnectString' that you should be able to copy to use in your code.

Or once the connection is created, you can use the oleDBConnection from the toolbox and just drop a connection on your form and set it's connection property to the connection that you created.

Thanks a lot dude, really appreciated! I still can't figure this out, I guess once I get that striaght, I will be able to go on my own, but for now, I'm stuck again...

Wouldn't it be supposed to work... but I get an "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" on MSDN they say it's cause by a msising data source, but the connection string I used it the one I created with the wizard... anyone cant point this one?

VB Code:

Private Sub frmMDI_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dsMnuCaptions As New System.Data.DataSet
        Dim dtCaptions As New System.data.DataTable("tbl_french_string")
 
        Dim oleDA As New System.data.OleDb.OleDbDataAdapter
        Dim oleCmd As New System.Data.OleDb.OleDbCommand
        Dim oleConn As New System.Data.OleDb.OleDbConnection
 
 
        'here place whatever connection you are using
        oleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jeanca.DSD-LAN\Desktop\Signs PRO\Signs PRO\signspro.mdb;"
        oleCmd.Connection = oleConn
 
 
        oleDA.SelectCommand = oleCmd
        dsMnuCaptions.Tables.Add(dtCaptions)
 
        'Put whatever select command you need
        oleCmd.CommandText = "SELECT STRING_MAIN_MENU_FILE FROM tbl_french_string"
        oleDA.Fill(dsMnuCaptions.Tables("tbl_french_string"))
 
        'Use the dataset with whatever columns you selected in the sql query
        'Dim menuitem As New System.Windows.Forms.MenuItem
 
        Me.MenuItem1.Text = dsMnuCaptions.Tables("tbl_french_string").Rows(0).Item("SELECT STRING_MAIN_MENU_FILE")
 
    End Sub

Try changing

oleCmd.Connection = oleConn

to

oleDA.Connection = oleConn

I get an error, that's why I had changed it...


'Connection' is not a member of 'System.Data.OleDb.OleDbDataAdapter'.

How about

oleDA.SelectCommand.Connection = oleConn

Sorry bout the wrong code there, I usually work in SQL Server.

You don't have to be sorry dude, you've helped me more than I could ask for!

still I have an "object is not set to an instance of an object" on that line:
oleDA.SelectCommand.Connection = oleConn

I'm totally desperate, I remember back in time, VB6 was soooooooo easy, now I just can't find anything in the VStudio help, neither on MSDN website... I look like a newbie, but I still have a degree as a computer analisys... earned it with vb6 though, I'm just way behind what .NET offers... damn, but thank you dude! and all of you!

i'd like to know, if at least, I've got the right order??

VB Code:

Dim dsMnuCaptions As New System.Data.DataSet
 
        Dim dtCaptions As New System.data.DataTable("french")
 
        Dim oleDA As New System.data.OleDb.OleDbDataAdapter
        Dim oleCmd As New System.Data.OleDb.OleDbCommand
        Dim oleConn As New System.Data.OleDb.OleDbConnection
 
 
        'here place whatever connection you are using
        oleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jeanca.DSD-LAN\Desktop\Signs PRO\Signs PRO\signspro.mdb;"
 
 
        oleDA.SelectCommand.Connection = oleConn
        dsMnuCaptions.Tables.Add(dtCaptions)
 
        'Put whatever select command you need
        oleCmd.CommandText = "SELECT STRING_MAIN_MENU_FILE FROM tbl_french_string"
        oleDA.Fill(dsMnuCaptions.Tables("french"))
 
        'Use the dataset with whatever columns you selected in the sql query
        'Dim menuitem As New System.Windows.Forms.MenuItem
 
        Me.MenuItem1.Text = dsMnuCaptions.Tables("french").Rows(0).Item("SELECT STRING_MAIN_MENU_FILE")

Finally I got something working fine... except for one reference...

VB Code:

Imports System.Data
Imports System.Data.OleDb
 
Module _mod
 
 
 
    Function loadLanguageString(ByVal lang)
        Dim parent As frmMDI
 
        ' create a connection string 
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jeanca.DSD-LAN\Desktop\Signs PRO\Signs PRO\signspro.mdb"
        Dim myConnection As OleDbConnection = New OleDbConnection
        myConnection.ConnectionString = connString
 
        Dim tblString As String = "tbl_french_string"
 
        ' create a data adapter 
        Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from " & tblString, myConnection)
 
        ' create a new dataset 
        Dim ds As DataSet = New DataSet
 
        ' fill dataset 
        da.Fill(ds, "string")
 
 
        ' write dataset contents to an xml file by calling WriteXml method 
        ' Attach DataSet to DataGrid 
 
 
 
        parent.MenuItem1.Text = ds.Tables("string").Rows(0).Item(2)
    End Function
 
 
End Module

How can I refer to a form from the module???


I get this error
An unhandled exception of type 'System.NullReferenceException' occurred in Signs PRO.exe

Additional information: Object reference not set to an instance of an object.


with this line
parent.MenuItem1.Text = ds.Tables("string").Rows(0).Item(2)