Posts by Lethugs

Say you have a main form and security form

After creating checkboxes and Combobox for the groupname and menu. Do the operation for updating the table for this menu's, (I used Updating since in adding username group, you have to insert this to your table for security like what I stated above, then just update it in security form).

On your security form load event, create select statement

Select * From Security where groupName = 'yourGroupName'

Set the check status of every checkbox you have:

Menu1.Checked = Datatable.Rows(0).Item("Menu1")
Menu2.Checked = Datatable.Rows(0).Item("Menu2")

where Datatable is the Datatable you used to store data from your select Statement
.Item("Menu1") - Table Column Name

On your Main Form, call security form laod event first to activate the form,

Call FrmSecurity.FrmSecurity_Load(sender,e)

Set the Combobox Selected Value to groupName of the user

FrmSecurity.Combobox.SelectedValue = 'UserGroupName'

Then Set the Status of your Menus:

If FrmSecurity.Menu1.Checked = True then
    FrmMain.Menu1.Enable = True
 Else
     FrmMain.Menu1.Enabled = False
 End If

Do all for your menus. This could take a lot of codes depending on your menu.

You can create a table which contains UseGroup and column with datatype bit for Menu Names, so you will have

UserGroup Menu1 Menu2 Menu3 and so on

Then Create a Form (say module access) which contains checkboxes that corresponds to every Menu and maybe a combobox for UserGroup so you can specify which Group you're setting access

Every time you add a userGroup make sure to add this also to your table containing your access settings, you can set the Menu columns to true (since it has a bit datatype) as default then you can edit its setting on your module access form later.

Before you load your main form which contains your menus, call first the module access form Form_Load to check for 'Checked' and 'Unchecked' checkboxes
To make sure you are looking for the right settings, set your combobox containing your groupName same as user's groupName, so if you logged in as Administrator, set the combobox selected item or selected value to administrator.

If say menu1 is check then enable your menu on your main form

menu1.Enabled = True

You have to check all the checkboxes to set the access setting of your menus

Date and Time are separate columns so i tried to concatenate them Convert(Datetime, Dates + Time, 0)

I tried to tweak your code and came up with this

select d.EID,  d.mintime, a.EID, a.maxtime
from (select EID, Dates, Max(Convert (Datetime, Dates + Time, 0)) maxtime from Tlogs group by Dates, EID) a

Join (Select Type, Convert(Datetime, Dates + Time, 0) as dat From Tlogs) b on b.dat = a.maxtime

Join (select c.EID,  c.mintime, c.Dates from (select EID, Dates, Min(Convert (Datetime, Dates + Time, 0) ) mintime from Tlogs group by Dates, EID) c ) d on d.EID = a.EID

Join (Select Type, Convert(Datetime, Dates + Time, 0) as dat From Tlogs) e on e.dat = d.mintime

where  a.maxtime between '01/01/2014' and '04/30/2014' and d.mintime > a.maxtime and b.Type = e.Type

But it displays all the data from the following dates with the same type
it should only display the next date only with same type

you're adding 1 day to get the next date while my data consists of indeterminate dates. The difference could be 2 days or more. Also we need to consider also the time. Im able to get the Transaction of max Time then compare it to the Transaction From min time of next date. But it shows also the max time of the next date

Yes sir, I was able to run your code. It shows even those with complete transaction

What I need to display is The first transation of the next date is the same with the previous date.

If for example the date 01/02/2014, it contains Types 'I' and 'O' so it means its a complete transaction, if the following Date for example is 01/03/2014 is with Type 'O' (meaning no 'I'), its an error in the data. That's what I need to display

Something like the last Type of a date is the same with the first Type of the following date

The system which the data is extracted should not accept a Type 'I' if the last Transaction Type is also 'I' or vice versa. So i need to show this system error

thank you urtrivedi for helping

I tried this code but not working, no error but not pulling the data

 Select Tlogs.ID, Tlogs.Type, Tlogs.Date, Tlogs.Time From Tlogs 

Left Join (Select ID, Type, Max(Convert (Datetime, Date + Time, 0) ) as Dat From Tlogs Group by ID, Type)  incs on incs.ID = Tlogs.ID  

Left Join (Select Tlogs.ID, min(Convert (Datetime, Tlogs.Date + Tlogs.Time, 0) ) as d, Tlogs.Type From Tlogs Group by Tlogs.ID, Tlogs.Type) inc on inc.ID = Tlogs.ID 

where inc.Type = incs.Type and inc.d > incs.Dat

and Tlogs.Dates between '01/01/2014' and '04/30/2014'

anybody who can help me?
I've been trying to figure this out for many days now

thanks

Lets say my time is already stored in datetime,

1. How can I retrieve the time only?
2. How will now I proceed with my query?

thanks again

Hi,

I need to compare record using sql ce

I have this data, can be thousand rows

ID      Type        Date        Time
02-0009  I          01/02/2014  8:00
02-0009  O          01/02/2014  18:00
02-0009  O          01/03/2014  18:00
02-0009  I          01/04/2014  8:00
02-0009  O          01/04/2014  18:00

For every date there should be a 'I' and 'O' type
If last Type for a date, for example 01/02/2014 is 'O', the Type for the next date should be 'I' and vice versa
The situation in dates 01/02/2014 with type 'O' and 01/03/2014 with type 'O' is invalid
The invalid record could be with type 'I' or 'O'

I need to query it to show this invalid record

please help, there really lots of limits in sql ce

thanks in advance

Hi pritaeas,

Sorry for late follow up, my code works now
thank you for sharing some knowledge

and because of that, for somebody who will encounter same problem as mine, here's what i used

"Select EID, Type, Dates, Time From Tlogs where Dates Between @f and @t " & usid & " and not exists (Select 1 From Tlogs Incount where Incount.EID = Tlogs.EID and Incount.Dates = Tlogs.Dates and Incount.Type = 'I') " & _
"or not exists (Select 1 From Tlogs Incount where Incount.EID = Tlogs.EID and Incount.Dates = Tlogs.Dates and Incount.Type = 'O') "

thanks again for helping sir

I will try to construct a query for that, but if you can give me ideas through code, that could be a great help for me

thanks again

How about if there IN's and OUT's have duplicates? Like what you say two or more IN;s or OUT's for the same date. How can I get the right result?

thanks

Hi,

I have a data, employee attendance record with following format

01-0002,I,4/21/2014,07:34:00
01-0002,O,4/21/2014,18:09:00
01-0002,I,4/22/2014,07:47:00
01-0002,O,4/22/2014,18:09:00
01-0002,I,4/23/2014,07:54:00
01-0002,O,4/23/2014,18:07:00

where I is in and O is out

The table contains lots of records for this saves records from the start

how can i search a record with no In or no Out from the date range specified?

I need to create a report for those employees with no In but with Out, or with In but no Out.
Another report needs to generate percentage of lates (individually or all)

For now this is the query I used but no luck

 query = "Select LID, EID, Type, Dates, Time From Tlogs where EID = @e and not exists (Select Types, Dates From Tlogs t1 where t1.Type = 'I' and t1.Dates = Tlogs.Dates)"

where EID is Emp ID (01-0002), Type is In or Out (I,O), Dates (4/23/2014), Time (18:07:00)

Thanks in advance

Problem solved. I used split method to chop the data into required parts.
Thanks tinstaafl for giving time helping me

After acquiring the desired result, I was asked to save it in database in splitted manner

01-0002,I,4/21/2014,07:34:00

There are 4 cols in my table, how can i loop through each record in a row?

result should be (in a row)

col1 - 01-0002
col2 - I
col3 - 4/21/2014
clo4 - 07:34:00

I tried this

`Dim ch As String

    For i = 0 To dt.Rows.Count - 1
        ch = Nothing

        If Not IsNothing(dt.Rows.Item("Rec") Then

            For Each s As Char In dt.Rows.Item("Rec"

                If s <> "," Then
                    ch = ch & s
                End If

                MsgBox(ch)
            Next


        End If

    Next

`
how can I assign each record in every col?

thanks again for the patience

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

Hi,

Im making a simple program which converts a encrypted text file the upload it to sql CE.
This is time in and out logs of every employee. The date is extracted from a machine and downloaded as encrypted file.
We managed to decrypt it by getting the equivalent value of each character.

I need to decrypt this file then save it to database (sql server 2008 r2)

Downloaded file looks like this, in plain text:

±²®ªÇª®°­°®­°®¯±ª®·¸²®¸±²B
´µ³ªÇª®°­°®­°®¯±ª®·¸²±¸³®N
´µ³ªÍª®°­°¯­°®¯±ª¯·¸®°¸²²T
±²®ªÍª®°­°¯­°®¯±ª°®¸®®¸°®9
±¯´ªÍª®°­°¯­°®¯±ª°®¸®®¸±²A
´µ³ªÇª®°­°°­°®¯±ª®·¸±¶¸±´X
±¯´ªÇª®°­°°­°®¯±ª¯®¸²¯¸²´C
±²®ªÇª®°­°°­°®¯±ª¯®¸²±¸°µA
´µ³ªÍª®°­°°­°®¯±ª¯·¸±¶¸±²]
´µ³ªÇª®°­°³­°®¯±ª®·¸³¯¸²µX

My code for now in uploading: (I used openfiledialog object to open the file)

cmd = New SqlCeCommand("Create Table TempDb (Rec Nvarchar (100)) ", Con)
        cmd.ExecuteNonQuery()

        Dim cmd2 As SqlCeCommand

        Using MyReader As New StreamReader(ofdBrowse.FileName)
            Dim fields As String()
            Dim line As String

            While Not MyReader.EndOfStream
                line = MyReader.ReadLine
                'Dim line As String = My.Computer.FileSystem.ReadAllText(ofdBrowse.FileName)
                fields = line.Split(vbNewLine)

                For Each field As String In fields

                    Select Case field

                        Case "®"
                            ch = "0"
                        Case "¯"
                            ch = "1"
                        Case "°"
                            ch = "2"
                        Case "±"
                            ch = "3"
                        Case "²"
                            ch = "4"
                        Case "³"
                            ch = "5"
                        Case "´"
                            ch = "6"
                        Case "µ"
                            ch = "7"
                        Case "¶"
                            ch = "8"
                        Case "·"
                            ch = "9"
                        Case "Ç"
                            ch = "I"
                        Case "Í"
                            ch = "O"
                        Case "«"
                            ch = "-"
                        Case "ª"
                            ch = ","
                        Case "­"
                            ch = "/"
                        Case "¸"
                            ch = ":"
                    End Select
                    rec = …

Hi,
Sorry for late update, it took time for me to figure this out, especially with a lot of work other than programming. Finally found out the problem. It lies on some conditions in my query. Ur suggestions work, Ive converted all the dates to datetime for more reliable result. Making my date column as nvarchar during database design pays a lot of problems to me now.

thank you all for your help. Really appreciate it

Hi,
The field is of type varchar with format mm/dd/yy, but in my query I am converting it to date. Im trying to figure out why solution of Jim does'nt work for me as expected.
Anyway here's an overview of my table

Tno(Pk)    TranNo      IID      User      DateAdded       DateDeleted
1           1           25      3           03/10/2014      NULL
2           2           18      4           03/15/2014      03/20/2014

so when I extract records which dateAdded < 03/19/2014, all records should be included since DateDeleted of TranNo 2 is > than the given date. Logically during that that TranNo is not yet deleted. Bu if the given date let say is 03/21/2014, TranNo 2 should not be included

thanks again

Hi Jim

I've tried that idea already, works fine but when DateDeleted is already greater than (later than) the specified date, the record is still included. I dont understand thinking that this condition should work. I also tried to convert this dates so that they will have the same format but still same result, is there any other way?

thanks again

Hi,

How to use in select statement with where condition comparing a Null value and with value?

I have this Statement

Select Col1, Col2, Col3 From Table where DateDeleted 'Here comes the problem

The condition is something like if DateDeleted (which is actually a date) is Null then get records with null DateDeleted, else it should compare the DateDeleted to specified date. If this DateDeleted is less than (or earlier) than the specified date then it will show the records, else it bypass it.

The idea of the select statement is to get all data with null DateDeleted and those having DateDeleted but less than (or earlier) than a given specific date

I am using vb.net 2010 and Sql server 2008 r2.

Hope somebody can help me for im running of time trying to figure this out

thank you in advance

Hi
Is there a way a vb form structure the same as its backgroud picture?
Its same with making the form invisible and let its background picture remain
Im trying to make a program that when it runs, it displays like widget.

thanks

I found a way to fix it. For now SQL Browser is running. Hope it will not give me headache again
Thanks for those who helped

How can i fix this?, I reinstalled it many times but no luck. For now I cant reinstall it anymore for some reasons.

Sorry for this, I can still uninstall the sql. What should I said is I can't reformat the unit anymore for some reasons

Yes I did, I even used the windows installation disk. Still Can't install it

Hi,
I already did reinstalling it many times, even cleaning the registry. I even used back up of registry, roll back system recovery, used microsoft fix it portable but same results.

Any idea why?

Having problem with looging in my server. Before it runs smoothly. While learning this system, I came accross the idea of exploring the contents of Microsoft SQL Server in Program Files. I found some exe files inside and tried to run them with the curiosity of what are those for, thinking developers will not put file that will ruin thier system :). The day after I can no longer log on to this server, I tried to troubleshoot it but ended up reinstalling the software. Error occured while installing, cannot continue due to sql browser could not start, I did some research and successfully installed it. Unluckilly still cant log in

A network-related or instance-specific error occured while establishing a connection to SQL Server.. etc. (Microsoft SQL Server, Error:2)

I checked Local Services, found sql browser automatic start up type but not starting. I tried starting it manually but another error occured

The SQL server Browser service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

How can i fix this?, I reinstalled it many times but no luck. For now I cant reinstall it anymore for some reasons.

Thanks in advance

Thanks for you guys for helping me, I did some revisions on my code. I declared a variable for my query, another variable for my conditions, then a just concatenate this variables depending on the conditions set by the user then pass this joined variable as my final query. It works for now since I have'nt encounter problems yet. Thanks again

In my query like
Select * from table where Branch=@branch and Type=1 and Status = 'Pending'
ho will I remove @branch parameter if the user wants to include all branches? not putting a value will generate error asking for the value of parameter since it is already included in my select statement?

Thanks

So that means no better approach for this? Do i need to query it individually?

Im using diffrent parameters for each cb. Some are ID's and some are Text

to clarify it further, example

Branch selected, Type = 1, Status and other info = All, the query would be

Select * from table where Branch=@branch and Type = 1

branch selected, Type =1, Status = Pending, Other info = all, the query would be

Select * from table where Branch=@branch and Type=1 and Status = 'Pending'

branch selected, Type = All, Status = Complete, Other Info = Sent, the query would be

Select * from table where Status = 'Complete' and OtherInfo = 'Sent'

and so on,

So there are different combination of conditions from those cb's. doing it per possible combinations would take a lot of queries. Any better solution for this?

thanks again

yes I want to filter data from one combobox to another. I have already storeproc, but the problem is f i make a default parameter for each condition, it will require a value even if the combobox is selected as 'All', which in that case no condition should be for that matter. The 'All' there is just a dummy selection which will show data now without condition. Every combobox represents different columns in my sql server

Hi
Just asking for a better solution for my approach in getting sql result with different multiple conditions based on comboboxes. I have a 4 comboboxes, for Branch, Type, Status and other info. User can select conditions from this comboboxes. The default value is "All", meaning no specific condition at all, but can choose from this cb's simultaneously. For example the user can choose specific type with all status and all other info, can also choose specific type, condition and all other info. in short he can select any combination of conditions from this cb's. I know I could query them individually but that would take me a lot of queries. Is there better approach for this

Thanks

You could do some work around if your using database

Try to upload your excel to a datatable then count the number of rows

Hi G waddell

Thanks for being supportive, for the ideas you gave, for sharing for precious time even your busy at work, appreciate it. I made some work arounds to accomplish this.

For those who will maybe encounter something related to this, this is how I did it

My StoredProc

Alter Procedure Sales (@in nvarchar(100), @out nvarchar(100) OUTPUT)ASDeclare @result nvarchar(100)My Select Statement here to get CountReturen @resultEnd`

Then in my Vb, I've created a sub to call the sp then show the result with my original select statement.

Private sub Sales

cmd = new sqlcommand("Sales")
cmd.CommandType = CommandType.StoredProcedure
cmd.parameters.add("@in",sqltype.nvarchar)
cmd.parameters.add("@out",sqltype.nvarchar,100)
cmd.parameters("@out").Direction = ParameterDirection.Output 'Calling the sp'

Select Barcode, ItemCode, Size, Type From Product left join etc... 'My orig select Statement'
Using sqladapter to fill datatable

Datatable.Columns.Add("AvailSales").SetOrdinal(4) 'Adding column manually'

Then i looped through this datatable to get the barcode of each record

For i as integer= 0 to datatable.rows.count -1
cmd.Paramater("@in").Value = Datatable.rows(i).Item("Barcode")
cmd.ExecuteNonQuery

Datatable.rows(i)("AvailSales") = cmd.Parameters("@out").Value
Next

'Code for displaying result to datagrid'
End Sub

If there's other better way to do this, that would be very helpful

Thanks again to G Waddell

Please somebody help me on this

Thank you for helping me solved this out

To clarify this,

1. I have a product table which contains primary info of products including barcode, itemcode, size, Type, etc. ("Product" Table)

2. I have a transaction Table which contains every transaction for every product including purchase, sales order, return, delivery, etc. ("Transaction" Table)

3. Now I need to check remaining product based from Transaction Table which is now in my first storedProc @Get_count

Select Code, (purchase - sales order + return - delivery) From Transaction Table (This if for every product type)

Where those inside the parenthesis are Joined Tables with different conditions mostly from transaction table also, which makes my statement long so I placed it inside storedProc

another storedProc named @Get_all

Select Code, (purchase - sales order + return - delivery) From Transaction Table (This if for every product regardless of type)

The same from the first but regardless with product type

Now on my final query I need to show product info with count of every product considering its type and count of it regardless of its type

Select Barcode, ItemCode, Size, Type, @Get_Count, @Get_all From Product

The barcode in this statement should the also the code for the storedProcs so that I can get the right counts, something like joining table in case i put this all queries in one statement
So the result should be

Barcode ItemCode Size Type @Get_count @Get_all
123 block 20x20 M 10 35
124 block 20x20 L 15 35
…

Hi,
I've been trying to do your code, i cant make it til now. how will i feed the data from my select statement to the function,

this is my select statement

Select Code, Size, @sale, @stock From Products - orig select statement

where @sales and @stock should be the result from the functions where it contains the select statement for the stored procedure

How will i feed the code from my select statement to those functions when running it generates error that @sale and @stock is not supplied? (the storedproc also contains the 'Code' in its select statement)

I also tried putting a value to those functions manually, it outputs the result of the function's select statement (the storedproc), not the orig statement with the value of @sale and @stock.

please help

thanks again

hi,
thanks for the reply, I've tried your code, what is the MyUserID in

cmd2.Parameters("@NoVisits").Value = GetNoOfVisits(MyUserID)?

My data comes from different tables and I guess the function would not work. My required output should be

Code AvailFor Sale AvailStock

G1 25 50
G2 10 15

The code there came from table, and another table contains transactions for every code, like purchased, sold etc. To get the availFor Sales and AvailStock I need to query those transactions tables, like getting the remaining inventory and the query for AvailFor sales and AvailStock are different StoredProc and are quite long, to display all these results I need to create another query which I need to add the results of those storedProc for every code. How will I do this considering those codes comes from my sql table?

thanks again

I already called a store proc in sql select Statement and as far as I know we include a CommandType.StoredProcedure for it to return a value. how will I add this stored proc in single select statement which now has text as commandType?

I tried this codes but nothing works

Select Barcode, sp_Get_count From Products

and

Select Barcode, Exec Get_count @st = N'Value' From Products

How will I fix the query?

the usual error is near exec, near From, cannot find sp_Get_count stored Proc..

Thanks again for helping

thanks for the idea however i haven't tried to do the first step, can you please show me example code returning the value of the stored proc then including it in single select statement and make it as a single query.

the second option I think would be not ideal since my query will be very long

thanks again

Hi, is it possible to combine Stored Procedure and Text Command Type in Select Statement?

I have a stored procedure that compute available items, it quite a bit long select statement,
now i need to call this statement in another select statement which is in text command type, how will i do it?

something like

Select Barcode, ItemCode, Get_itemCount From Product

where Get_itemCount is my storedProc

Im using vb.net and Sql server 2008 R2

thanks in advance

Your a savior, my query runs in sql server, just need some modification for sql ce. Thanks pritaeas.

actually there's only one logo. I have a table where I edit company information including its logo. This logo need to show in Crystal report. Whatever Name, add the system extracts from database. only one logo is used.

how can I query it using sql ce?
thanks again