Posts by VIPER5646

Hi all
I have a Mdi Child Fom being oppened from the parent the form opens but the Load event will not fire UP.
Doesn't the load event suppose to fire up every time the form is opened?
Here isthe code usedto open the form from the Parent and also the load even of the child form.

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim mdiChildForm As New Input_frm
        mdiChildForm.MdiParent = Me
        If Not CheckIfOpen("Input_frm") Then
            mdiChildForm.Show()
        End If
    End Sub

      Private Sub Input_frm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        MsgBox("Loaded")
    End Sub

Thanks In Advance

Thank you all for your help
I Have Solved it by adding apostrophes to the date and to 'Saturday' and 'Sunday'

Thanks IIm for your reply butI had to change the procedure to A function because the procedure does not return a value.
so now I have this fuction that there is no error messag in it.

delimiter $$
CREATE DEFINER=`root`@`localhost` FUNCTION `PrcssDate`( fDate Date, fVal int) RETURNS date
BEGIN
Declare x int default 1;
Declare NewDate Date;
WHILE x <= fVal DO
set NewDate=Date_SUB(fDate,interval x Day);
CASE 
WHEN DAYNAME(NewDate)=`Saturday`
THEN set fVal = fVal+1;
set x=x+1;
WHEN DAYNAME(NewDate)=`Sunday` 
THEN set fVal = fVal+1;
set x=x+1;
ELSE
set x=x+1;
return NewDate;
End Case;
End While;
END$$

My isue with it is that when I try to call the function using SELECT Production.prcssDate(2013-04-04,1) As P1; I get the error "SELECT Production.prcssDate(2013-04-04,1) As P1 LIMIT 0, 1000 Error Code: 1292 Incorrect date value: '2005' for column 'fDate' at row 1"

Hi all I'm trying to write a Procedure to subtract days from a data but Ignoring weekends.
This is what I have so far but I have a Syntax error Unexpected END, Expecting ;
I have put a ; after the last END but still get the same error.

DELIMITER $$ 
CREATE PROCEDURE `production`.`new_procedure` (in fDate Date, fVal int) 
BEGIN 
Declare x int default 1; 
Declare NewDate Date; 
WHILE x <= fVal DO 
set NewDate=Date_SUB(fDate,interval x Day); 
SELECT 
CASE DAYNAME(NewDate) 
WHEN `Saturday` THEN fVal=fVal+1 
WHEN `Sunday` THEN fVal=fVal+1 
Else x=x+1 
END 
End 
END$$

By adding the Debug.WriteLine(UpdatequeryString)the immediate window displays on the first Update
UPDATE Status_tbl SET tobedesign= @1 WHERE CustomerID=1724
the second update it displays the same string UPDATE Status_tbl SET tobedesign= @1 WHERE CustomerID=1724 but I also receive (A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll )

The

Option Strict On

did display other error.
but the problem still existes.

Thanks for the input TnTinMN

Thanks for your reply.

statustype will hold the name of the field name being updated.
Since there are 8 status to an order and not to creat a query for all of them I stored the field Name in the statustype that reflects the checkbox being checked .
So if the Hold checkbox is being checked or unchecked then the Statustype (field name) would be Hold.

Hi all
I have multiple checkboxes to be used as the status of a certain order.
another words as the order is being processed the user will check the status and a date will be inserted into an access table.
Now my isue is when a checkbox is checked for the first time it inserts the date into the table but if the user unchecks it atomaticly updates the table with a null value with no errors. If the same checkbox gets checked again
or any othercheck box I get the error "Operation must use an updateable query".
Im puzzeled because if I use the same code to insert and also the same code to update does anyone know why it works the first time it is used but not the second time.

here is the Update Code.

Public Sub Update_Status()
        Try
            Dim db As New Connection
            db.FilePath = FrmMain.DBCredentials.DBPath
            db.PalavraPass = FrmMain.DBCredentials.Pass
            db.connect()
             Dim UpdatequeryString As String = "UPDATE Status_tbl SET " & Statustype & "= @1 WHERE CustomerID=" & customerID
            Dim dbcmd As New OleDbCommand(UpdatequeryString, db.con)
            db.con.Open()
            With dbcmd.Parameters
                If StatusDate.Year <> "1900" Then
                    .AddWithValue("@1", StatusDate)
                Else
                    .AddWithValue("@1", DBNull.Value)
                End If
            End With
            dbcmd.CommandType = CommandType.Text
            dbcmd.CommandText = UpdatequeryString
            dbcmd.ExecuteNonQuery()
            db.con.Close()
        Catch ex As Exception
            MsgBox("STATUS.Update_Status: " & ex.Message)
        End Try

    End Sub

Hi
I have a column (ColumnC) in an access query which extracts the notes from column A and ColumnB and seperates them with a comma.
This is the expressoin I use in access (Note: [RecheckNote] & IIf(Not IsNull([RecheckNote]) And Not IsNull([CTNote]),",","") & [CTNote] )

What I would like is to write an Expression in RDLC report that everytime it encounters a comma it would do a line return and Put "-" after the line return.
Thanks in advance

Thank You all for your replies
I guess all I was missing from my original QUERY was the table name Invoiced after the AND.
Here is the working QUERY

   SelectQueryString = "SELECT * FROM invoiced_filter & _ 
   WHERE (Invoiced >= #" & fromdte & "# AND Invoiced <= #" & todte & "#)"

Thanks Reverend for your reply.
But the error still existes. Accept now the error is
"Syntax error (missing operator) in query expression 'Invoiced > #01/01/13# AND < # 05/01/13#'

Hi
I'm trying to Select Data from an Access DB.
I wrote this query to Select all Invoices that were Invoiced between two dates.
I get the following error
Syntax error (missing operator) in query expression 'Invoiced >=02/01/13 AND <= 05/01/13'

"SELECT * FROM invoiced_filter WHERE Invoiced > " & fromdte & " AND < " & todte

Thanks in Advence

thanks Adam for your reply.
I have tried and it works.

Hi all
I have an Access database Table and one of the columns has a date value the column name is closing.
I would like to do an order by closing asc the null values are allways on top and I would like it to be at the bottom.
I have googled for a solution and I came up with this

Dim SelectQueryString As String = "SELECT * FROM " & Filter & " order by case when Closing is null then 1 else 0 end, Closing"

When executing this query I get Unrecognized keyword WHEN error.
thanks in advance

Thanks tinstaafl for your reply.
With the link you provided and other Google searches I Created a Function that seams to work.

 Private Function GetBusinessWeeks(ByVal Date1 As Date, ByVal Date2 As Date) As Long

        Dim days As Integer
        Dim weeks As Integer
        days = DateDiff(DateInterval.Day, Date1, Date2) + 1
        Weeks = Days \ 7
        Days = Days Mod 7
        If Days > 0 Then
            If date1.DayOfWeek = DayOfWeek.Sunday Or date2.DayOfWeek = DayOfWeek.Saturday Then
                days = days - 1
            ElseIf date2.DayOfWeek < date1.DayOfWeek Then
                days = days - 2
            End If
        End If
        Dim Weekes As Long = (weeks * 5 + days) / 5
        Return Weekes
    End Function 

Hi all
I have the folowing to calculate the Number of weeks between two dates.
The OpenDate= Nov-1-12 and closingDate= Nov-29-12.
When Executing the the NumberOfWeeks=3
If I go to the calander 4 weeks why is the NumberOfWeeks=3

NumberOfWeeks = DateDiff(DateInterval.Weekday, OpenDate, ClosingDate)

Thanks In Advance.

Thanks John
I have solved it by loading the dates into an Arraylist.
Now when the cell is formatted I will have the dates to be calculated.

Thanks John for your reply
It is a 0 based count.
Yes the BackColor = Color.Red it's afecting column 8 but I would like if possible to affect column 7 after checking the date that is on column 8 to make the calculation.

Hi all
I'm trying to shade a cell based on a date difference between column 7 & 8.
It seams to work accept column 8 seams to be the one being shaded instead of column 7.
I have googled but can't find a solution to have the calculatin being made on when column 8 being loaded but the formatting being applied to column 7.

Private Sub gvStatus_CellFormatting(ByVal sender As Object, ByVal e As DataGridViewCellFormattingEventArgs) Handles gvStatus.CellFormatting
        Dim Closing, Tobemeasured, tobedesign As Date
        Dim NumberOfWeeks As Integer

        If Not IsDBNull(gvStatus.Item(6, e.RowIndex).Value) Then
            Closing = gvStatus.Item(6, e.RowIndex).Value
        End If
        Select Case e.ColumnIndex
            Case 7
                If Not IsDBNull(e.Value) Then
                    Tobemeasured = e.Value
                End If
            Case 8
                If Not IsDBNull(e.Value) Then
                    tobedesign = e.Value
                Else
                    NumberOfWeeks = DateDiff(DateInterval.Weekday, Now(), Closing)
                    If NumberOfWeeks > 5 Then
                        e.CellStyle.BackColor = Color.Red
                    End If
                End If

Hi
In my program I have a Dataset that populates a Rdlc file and it was working Fine until I Encrypted the Accdb file and now when I run the App I get "Not a valid password" error at this line

Me.Services_QryTableAdapter.Fill(Me.CFIDataset.Services_qry, RprtID)

I have tried Modifying the connection for the dataset and also recreating the dataset and was unsuccessful .
How can I accomplish this.
Thank you

thanks Oxiegen for your reply.

Hi all.
I have a program in vb.net and with it I have an expiration Key.
I would like this key to be valid for 3 moths for example.
I don't want to use the users system.date because to prevent them from changing the date.
I there a way I could use for example a public server's date time instead.
thanks

Thank you for your reply
It worked

hi
I have successfuly created an access database with vb.net accept it is not password protected.
Can someone help me to password protect this DB.
Here is the code I use.
Thank you

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Try
            Dim cat As New ADOX.Catalog
            Dim tbl As New ADOX.Table
            Dim fld As New ADOX.Column
            If CheckPath("C:\Customer\Customer.accdb") Then
                My.Computer.FileSystem.DeleteFile("C:\Customer\Customer.accdb", _
              FileIO.UIOption.AllDialogs, FileIO.RecycleOption.SendToRecycleBin)
            End If
            cat.Create("Provider=Microsoft.ace.OLEDB.12.0;Data Source=C:\Customer\Customer.accdb")

            With tbl
                .Name = "Cliente"
                .ParentCatalog = cat
                .Columns.Append("ID", DataTypeEnum.adInteger)
                .Columns("ID").Properties("AutoIncrement").Value = True
                .Columns("ID").Properties("Nullable").Value = False
                .Columns.Append("FirstName", DataTypeEnum.adVarWChar, 75)
                .Columns("FirstName").Properties("Nullable").Value = True
                .Columns.Append("LastName", DataTypeEnum.adVarWChar, 75)
                .Columns("LastName").Properties("Nullable").Value = True
                .Columns.Append("Age", DataTypeEnum.adInteger)
                .Columns("Age").Properties("Nullable").Value = True
            End With
            cat.Tables.Append(tbl)
            cat = Nothing
            Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

Thanks you solved it.
Removing the First call for New Attachment(Attacmentfile)did the trick.

Yes the Dispose was called before attempting the delete.
This is the actual error:
System.IO.IOException: The process cannot access the file 'C:\CFI\33Glasgow.pdf'
because it is being use by another process.
at System.IO._error.winIOErroe(Int32 errorCode.String maybeFullPath)
at System.IO.File.Delete(String Path)
at CFI.Cfi_Report.SendMail() in C:\Users\Admin\Documents\Visual Studio 2010\CFI\Cfi_Report.vb:Line106

Thanks thines01 for your reply
I added the line of code you specified right after the email was sent but I still get
the system.IO.IOException.
I also tried mail.Attachments.Dispose() and no luck.

Hi all
In the following Sub I'm sending an Attachment when the email has been sent I would like to delete the attachment but I get an system.IO.IOException Error. the file I'm trying to delete is being used by another process.
How can I detect if the process has finished.

Thanks
Happy New Year to everyone

Private Sub SendMail()
        Try
            Dim cfiAttach As Net.Mail.Attachment
            Dim Attacmentfile As String = AttDir & fileName & ".pdf"
            Dim SmtpServer As New SmtpClient()
            Dim mail As New MailMessage()
            SmtpServer.Credentials = New Net.NetworkCredential(diaSettings.txtMail_User.Text, diaSettings.txtMail_Pass.Text)
            SmtpServer.Port = smtpPrt
            SmtpServer.Host = SmtpHst
            mail = New MailMessage()
            mail.From = New MailAddress(Mail_user)
            mail.To.Add(Mail_TO)
            ' mail.Bcc.Add(BBC_MailTO)
            mail.Subject = fileName
            mail.Body = ""
            cfiAttach = New Attachment(Attacmentfile)
            If File.Exists(Attacmentfile) Then
                cfiAttach = New Attachment(Attacmentfile)
                mail.Attachments.Add(cfiAttach)
            Else
                MsgBox("Attachment Not Found")
            End If
            SmtpServer.Send(mail)
            MsgBox("Sent")
           [U]System.IO.File.Delete(AttDir & fileName & ".pdf")[/U] 
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

Thanks Bilal for trying
But I recreated a new RDLC and I get it right this time.

Thanks Bilal for your Reply.
Yes it is displaying the report properly.
I'm using reportviewer not CrystalReportViewer I do not have the printreport() option.
Anyways I'm exporting the reports to a PDF.
When I click printlayout or export it I do get the second page with the header only.

I have a reportviewer that prints a second empty page with the header only nothing in the body.
I have searched for this issue and I learned that the Width of the report body should be equal to the page width minus the right and left Margins.
So I have set the page margins to 0 and shrunk the page width considerably and I still get a last empty page.
What else can I do to eliminate that empty page.
Thank you

Thanks GeekByChoice

It works great.
The dictionary object is something new to me.
I will be looking into it more.

I have two arraylists. The JobArraylist gets adresses from a database table and JobIDArray Gets the ID for those addresses.
I'm populating a Combobox with the JobArrayList which is working ok.
When I select a an address in the combobox I use the sectectindex to get the AdressID from JobIDArray.
My Issue is when I have duplicate addresses the JobIDArray always returns the the ID for the last Address. not the select one.
Thank you
this code gets data from the database and populates the Combobox.

Private Sub PopulateSearch()
        cbxSearch.Items.Clear()
        Dim JobArraylist As New ArrayList
        Dim Query As String = "SELECT JobID,JobAddress FROM JobName_tbl Order by JobAddress"
        Dim CFIConnection As New Connection
        CFIConnection.FilePath = "C:\CFI\FilePath.Txt"
        CFIConnection.connect()
        Dim cmd As New OleDb.OleDbCommand(Query, CFIConnection.con)
        cmd.CommandType = CommandType.Text
        cmd.CommandText = Query
        CFIConnection.con.Open()

        Dim dr As OleDb.OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            If Not dr.IsDBNull(1) Then
                JobArraylist.Add(dr.GetString(1)) ' arraylist will be used to populate combobox
                JobIDArray.Add(dr.GetInt32(0)) 'When the Selected Item on the Combobox is Selected it gets the Item ID
            End If
        End While
        For Each Job In JobArraylist
            cbxSearch.Items.Add(Job)
        Next
        dr.Close()
        CFIConnection.con.Close()
    End Sub

This Code gets the ID for the selected Address.

Private Sub cbxSearch_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxSearch.SelectedIndexChanged
 Dim x As Integer = cbxSearch.SelectedIndex
        If cbxSearch.SelectedItem IsNot Nothing Then
            NewCustomerID = CInt(JobIDArray.Item(x))
            GridLoad = True
            UpdateAddress = True
            btnAddJob.Enabled = False
            LoadListGroup()
            btnCustom.Enabled = True
            Dim JOBSearch As New JobAddress
            JOBSearch.Address = cbxSearch.SelectedItem.ToString
            JOBSearch.NewSearch()
End Sub

try the following

Me.ReportViewer1.LocalReport.EnableExternalImages = True
      Dim img As String = "file:\\[U]FilePath+Filename[/U]"
      Dim path As New ReportParameter("LogoPath", img)
      ReportViewer1.LocalReport.SetParameters(New ReportParameter() {path})
      Me.ReportViewer1.RefreshReport()

wrong post

A good step in debugging something like this is to get the actual statement and either run it in the db or at least go through it.

use debug.print cmd.commandtext just before the ExecuteNonQuery and verify that you are running what you think you are.

Thanks All for your help but I solved it by putting the .Parameters.AddWithValue in the same order as in the query.
:)

Hi all

With the following SQL " Dim Query As String = "UPDATE JobName_tbl SET [JobDate]=@NewDATE, JobAddress=@Newaddress Where JobID=@ID"
nothing happens nothing gets updated nor do I get an error.
But as soon as I remove the [JobDate]=@NewDATE, it works fine.
Hope someone can help me with this situation.
Here is my code:
Thank You

Public Sub UpdateAddress(ByVal JobID As Integer, ByVal JobUpdate As String, ByVal NewDate As Date)
        Try            
            Dim Query As String = "UPDATE JobName_tbl SET [JobDate]=@NewDATE, JobAddress=@Newaddress Where JobID=@ID"    
            Dim DBcon As New DBConnection.Connection
            DBcon.FilePath = "C:\CFI\Filepath.txt"
            DBcon.connect()
            DBcon.con.Open()
           Dim cmd As New OleDb.OleDbCommand(Query, DBcon.con)
                With cmd
                .CommandType = CommandType.Text
                .CommandText = Query
                .Parameters.AddWithValue("@Newaddress", JobUpdate)
                .Parameters.AddWithValue("@ID", JobID)
                .Parameters.AddWithValue("@NewDATE", NewDate)
                .ExecuteNonQuery()
            End With
            DBcon.con.Close()
        Catch ex As Exception
            MsgBox("JobAddress_UpdateAddress" & ex.Message)
        End Try
    End Sub

Thank you ALL

I have solved by using the following QUERY in the TableAdapter.

SELECT        [Date], Address, First Name, Orders
FROM            Report_qry
WHERE        (Address = ?)

Thanks Mitja for your reply.
Now after the Datatable being filtere how can we go about adding it to the .RDLC

Where is what I being trying but does not work
The datagrid has data but not the reportviewer

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        GetData()
        Me.DataGridView1.DataSource = GetData()
        With Me.ReportViewer1.LocalReport
            .ReportPath = "Report1" & ".rdlc"
            .DataSources.Clear()
        End With
        Dim rptDataSource As ReportDataSource
        rptDataSource = New ReportDataSource("Report1", GetData)
        Me.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
    End Sub

    Private Function GetData() As DataTable
        Dim mdt As New DataTable("reports")
        Dim m_dr As OleDbDataReader
        Dim mycon As New Connection
        mycon.FilePath = "C:\Report\Report.txt"
        mycon.connect()
        Dim query As String = "Select * from Report_qry WHERE [Address]=@address "
        Dim cmd As New OleDb.OleDbCommand(query, mycon.con)
        cmd.CommandType = CommandType.Text
        cmd.CommandText = query
        With cmd
            .Parameters.AddWithValue("@address", TXTAD)
        End With
        mycon.con.Open()
        m_dr = cmd.ExecuteReader
        mdt.Load(m_dr)
        Dim dataset1 As New DataSet
        dataset1.Tables.Add(mdt)
        Return mdt
    End Function
End Class

Thanks sgt_tosty for your reply.
Yes I have a text box that the user will type in the customer's name.
But my problem in not how to query.
My difficulty is the data table has been created in the Designer which now seams to be the same results every time I run the Report.
I need to find out is how can I replace the query that I created in the designer with one that I would like to creat in code.

Hi all
I created a RDCL in the designer.
I would like the User to Determine what is shown in the Report.
How can I change the query at runtime? (ex. changing the customer Name)

This is the query that I have in the properties
SELECT [Date], Address, Customer, Orders
FROM Report_qry
WHERE (Customer= 'JohnDoe')

Thanks

Hi
I agree with you both.
I my self started with vb2008 , with the help of DaniWeb and Google I learned allot.
I still have lots to learn
Good Luck

thanks GeekByChoiCe and shahanDev for your help including the link

Hi all
I'm using a dialog form with two bottons and two Radio buttons.
I'm oppening this Dialog when a button is clicked on the parent form.
My situattion is when the Dialog opens the code in the button event continues to execute, but I only want it to continue to execute only after a button from the dialog form have been clicked

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
            If multipleEXT = 2 Then
            Extension.Show()
            End If
'More Code

Thanks in advence

Thanks adam for your yelp

Well good question.
But I'm just starting into the developers world.
I kind of started late I'm near my 50's and hope my memory won't go so soon.

HI ALL!

Let me start by letting you know that I'm still new to VB.
All I know about Programing is with the use of google, Videos, and some good help from DaniWeb.
I'm doing a program which searches datagrid colums with some classes That I have wrote. I have 5 textbox to enter the text to search for and when text is entered it searches for that text .
My question is by having the textchange event reference the class to search for the text I'm woundering if the program will slow down because it it's constantly referencing the same class.
Hope this makes any sence.
Thanks in advance.

Thanks all
I had forgotten to instantiate ModelBuilder_Relation.
At least I asumed I had it wright instead of Dim ModelBuilder_Relation as new Arraylist I had Dim ModelBuilder_Relation as Arraylist. Now it seams to be working

I cant seam to mark this thread as solved may be because I did not start this thread .

Thank you all for your replies.

Based on the code I posted I closed it by using m_dr.close.
If that is correct than they are all closed and I still get the same error.