Hi, I have an Access Data table with three fields (date, tippingB, and precip). I am using dataGridView to view this table and make changes. The third field in the table (precip) is dependent on second field (tippingB). I was wondering if there is a way to use a formula in the field so that when I put a number in the second field, third field automatically gets updated. The last step of the code is updating the access table in the access database (that works so far). So, my question is if one could use a formula for a column in a dataGridView and also bind it to the dataTable so that when update command is used, the database gets updated. Thanks, Amanjot

Hi, I have data in Access Database which I am filling in the DataGridView to QA/QC raw data downloaded from a climate station. I am having trouble updating the database using update command. I am using following steps: 1. Locating access database: Private Sub AccessDataFileButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AccessDataFileButton.Click OpenFileDialog2.Filter = "Microsoft Access File(*.mdb)|*.mdb|Microsoft Access File(*.accdb)|*.accdb|All files (*.*)|*.*" OpenFileDialog2.Title = "Input Data File with Real-time Water Quality Time Series" If OpenFileDialog2.ShowDialog = DialogResult.OK Then accessDataFile = OpenFileDialog2.FileName 'MsgBox("accessFile= " & OpenFileDialog2.FileName) Me.AccessFileTextBox.Text = accessDataFile End If End Sub 2. Filling DataGridView: Sub fillDataGrid1() ' Read Access Table to find date and time of the last data Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDataFile Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim inpTable As String = "rawClimateData" 'Access database table Dim cmd As String = "Select * from " & inpTable '& " ORDER BY mdateTime" Dim dbCmd As OleDbCommand = New OleDbCommand(cmd, myConnection) ' create a data adapter Try da = New OleDbDataAdapter(cmd, myConnection) Catch ex As Exception ' Catch the error. MsgBox(ex.ToString) ' Show friendly error message. End Try ' create a new dataset Dim table As New DataTable myDataset = New DataSet() Try Me.da.Fill(table) da.Fill(myDataset, inpTable) Me.DataGridView1.DataSource = table Catch ex As Exception MsgBox(ex.ToString) End Try myConnection.Close() End Sub 3. Updating database (update button) Private Sub TryTry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upDate.Click Try Me.da.Update(Me.myDataset.Tables("rawClimateData")) Me.myDataset.AcceptChanges() Catch ex As Exception MsgBox(ex.Message) End Try MsgBox("upDateComplete") End Sub In some examples I have seen connecting database file while Fo

Hi, I am trying to fill the gridview with the data from an Access table for a range of dates.

    Dim cmd1 As String = "Select \* from " & inpTable & " Where mdateTime = '29/09/2011'" 
    MsgBox("cmd= " & cmd1)
    Dim da As OleDbDataAdapter
    Try
        da = New OleDbDataAdapter(cmd1, myConnection)
    Catch ex As Exception    ' Catch the error.
        MsgBox(ex.ToString)   ' Show friendly error message.
    End Try

    Try
        Dim table As New DataTable
        da.Fill(table)
        DataGridView1.DataSource = table
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
    myConnection.Close()

Command as it appears in the MsgBox Select * from rawClimateData Where mdateTime > 30/09/2011 10:45:00 AM The query works if I am not filtering the data base; however, with filter I get the syntax error "missing operator". Any suggestion??

Thanks for your help. The program finally works. The last surprise I had was that when the parameters are added, they need to be in the same sequence as they are in the dataBase. Here is the final code: accessDataFile = AccessFileTextBox.Text Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDataFile Try Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim mdateTime As Date = "07/07/2011 11:00:00" Dim Precip As Single = 3.2 Dim TippingBucket As Integer = 2 Dim cmd As String = "insert into rawClimateData (mdateTime,TippingBucket, Precip) VALUES(@mdateTime, @TippingBucket, @Precip)" Dim dbCmd As OleDbCommand = New OleDbCommand(cmd, myConnection) MsgBox(cmd) ' Parameter collection dbCmd.Parameters.Add("@mdateTime", OleDbType.Date).Value = mdateTime 'Convert.ToDateTime(mdateTime) dbCmd.Parameters.Add("@TippingBucket", OleDbType.Integer).Value = TippingBucket dbCmd.Parameters.Add("@Precip", OleDbType.Single).Value = Precip dbCmd.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.Message) End Try

Thanks for catching that. I have improved code further, however, still no success. The same error is popping up: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error (missing operator) in query expression '07/07/2011 11:00:00 AM'. Here is how the code looks now: accessDataFile = AccessFileTextBox.Text Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDataFile Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim mdateTime As Date = "07/07/2011 11:00:00" Dim Precip As Single = 3.2 Dim TippingBucket As Integer = 2 Dim cmd As String = "insert into rawClimateData (mdateTime, TippingBucket, Precip) VALUES (" & mdateTime & " , " & TippingBucket & " , " & Precip & ")" Dim dbCmd As OleDbCommand = New OleDbCommand(cmd, myConnection) MsgBox(cmd) ' Parameter collection dbCmd.Parameters.Add("@Precip", OleDbType.Single).Value = Precip dbCmd.Parameters.Add("@TippingBucket", OleDbType.Integer).Value = TippingBucket dbCmd.Parameters.Add("@mdateTime", OleDbType.DBDate).Value = mdateTime dbCmd.ExecuteScalar()

Thanks for the link. It was good to get updated with the parameter collection stuff. You are right, now it makes the code more flexible. However, I still have issues with the date. The program works fine if I don't "insert into" dataTime field but when I include this field, I get syntax error. Any tips for that?

Dim mdate As Date
mdate = Convert.ToDateTime("07/08/2011 12:00:00")
Dim dbCmd As OleDbCommand = New OleDbCommand

' Parameter collection
Dim parameter As New OleDbParameter()
dbCmd.Parameters.Add("@dateTime", OleDbType.Date).Value = mdate
dbCmd.Parameters.Add("@TippingBucket", OleDbType.Integer).Value = TippingBucket

            Dim cmd As String = "insert into rawClimateData (dateTime, TippingBucket) VALUES (" & mdate & " , " & TippingBucket & ")"

dbCmd = New OleDbCommand(cmd, myConnection)
dbCmd.ExecuteScalar()

Thanks very much for the quick reply. I believe you could sense that I am a rusted programmer. Could you explain little bit on what you mean by parameters collection!!

Hi, I am trying to populate access database with a small program in VB; however, getting the syntax error "Syntax Error (missing Operator) in query expression '07/08/2011 12:00:00 PM'." If I remove the date filed in the database, the program works fine. Therefore, the problem seems in the date field. In access database, the format of the date field is General Here is the code:

Dim mdate As Date
mdate = "07/08/2011 12:00:00"
Dim cmd As String = "insert into rawClimateData VALUES (" & mdate & ", 12:50 , " & TippingBucket & " , " & Precip & " , " & AirTemp & " , " & SolarRad & " , " & Evap & " , " & windSpeed & " , " & windDirection & " , " & SoilTemp & " , " & SnowDepth & " , " & RH & " , " & BaroPressure & " , " & BatteryVoltage & " , " & SoilMoisture & ")"
MsgBox("cmd= " & cmd)
dbCmd = New OleDbCommand(cmd, myConnection)
dbCmd.ExecuteScalar()

Any help would be greatly appreciated. Thanks, Amanjot

Hi! I want to develop a small program that has hyperterminal functionality to communicate remotely with a data logger and download file using dial-up modem. Any suggestions or links!! Thanks AJ

I am using following code to populate dataTable and then updating Access Table. I am getting error "OleDbException was unhandled" "Syntax error in INSERT INTO statement. Any help would be appreciated. Thanks, Aman Private Sub dataGridAccess() Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessFile Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim myTable As String = "RealTimeWQActual" Dim myCmd As String = "Select * from " & myTable & " ORDER BY sdate" ' create a data adapter Dim da As OleDbDataAdapter Try da = New OleDbDataAdapter(myCmd, myConnection) Catch ex As Exception ' Catch the error. MsgBox(ex.ToString) ' Show friendly error message. End Try MsgBox(myTable & " " & myCmd) ''Dim dr As New OleDbDataReader ' Create command builder Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(da) ' create a new dataset Dim ds As DataSet = New DataSet ' fill dataset da.Fill(ds, myTable) ' Insert code to populate the DataTable with rows. ' Set the DataSource and DataMember of the DataGrid control. DataGrid1.SetDataBinding(ds, myTable) Dim dt As DataTable = ds.Tables(myTable) Dim rrows = dt.Rows.Count MsgBox("rrows= " & rrows) ' NewRow to create a DataRow. Dim row As DataRow row = dt.NewRow() ' Then add the new row to the collection. row("SDATE") = "1" row("STIME") = "1" row("Parameter") = "1" row("Value") = "1" row("d1") = "1" row("d2") = "1" dt.Rows.Add(row) '**** error comes at the next step *** da.Update(ds, myTable) '**** Program runs without the previous step and dataTable shows the added row myConnection.Close() End Sub

Hassan, could you please share what number did you use for the port and what did you use for SMTPServer.EnableSsl!! Thanks, Aman

Hi, I am trying to send an email for my gmail account using SMTP code in VB.net; however, the email is not getting sent. I am using the code shown below. Kindly suggest what am I doing wrong. Thanks. Aman

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

    'Start by creating a mail message object
    Dim MyMailMessage As New MailMessage()

    'From requires an instance of the MailAddress type
    MyMailMessage.From = New MailAddress("abc@gmail.com")

    'To is a collection of MailAddress types
    MyMailMessage.To.Add("abc@yahoo.com")

    MyMailMessage.Subject = "GMail Test"
    MyMailMessage.Body = "This is the test text for Gmail email"

    'Create the SMTPClient object and specify the SMTP GMail server
    Dim SMTPServer As New SmtpClient("smtp.gmail.com")
    SMTPServer.Port = 465
    SMTPServer.Credentials = New System.Net.NetworkCredential("abc", "cba")
    SMTPServer.EnableSsl = True

    Try
        SMTPServer.Send(MyMailMessage)
        MessageBox.Show("Email Sent")
    Catch ex As SmtpException
        MessageBox.Show(ex.Message)
    End Try

End Sub

I am trying to send an email from a Vb.code and the following outlook security warning pops up: "A program is trying to address emial address....." How could I overide this security message!! Thanks, Aman

Thanks very much Dave, it works now.

Hi, I have inserted an image from an existing file which I want to close. I have tried using dispose() command (shown below); however it gives an error. Could some one suggest what would be the command to close the file after the image has been displaced? Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click 'Temperature button pressed Dim iImage As Image iImage = Image.FromFile("C:\Aman\ZedGraph\temp.jpeg") 'PictureBox1.Image = Image.FromFile("C:\Aman\ZedGraph\temp.jpeg") PictureBox1.Image = iImage iImage.Dispose() End Sub Thanks Aman

Thanks Dave for the tip. It worked for my application; however, do you know the statement for saving a ZedGraph chart/graph as an image (jpg, bmp etc.)! Aman

Hi, I want to include/create a chart in my program which displays titles of x and y axis and then saves the chart/graph as an image file. What would be the robust way to do that!! Thanks, Aman

Thanks Steve. I tried the option you suggested; however, it didn't work. Thanks, Amanjot

Hi, I am using dataset for playing with data and wanted to create an additional column that stores logarithm (log) of another column. I tried to use number of options including the one below but still have trouble making the program work. dtRec.Columns.Add("sumXY", Type.GetType("System.Double"), Math.Log("recflow")) recflow is an existing column of the dataset and I want log values of reflow to be stored in new sumXY column. Any suggestions!! Amanjot

Hi, I am using dataset for playing with data and wanted to create an additional column that stores logarithm (log) of another column. I tried to use number of options including the one below but still have trouble making the program work. dtRec.Columns.Add("sumXY", Type.GetType("System.Double"), Math.Log("recflow")) recflow is an existing column of the dataset and I want log values of reflow to be stored in new sumXY column. Any suggestions!! Amanjot