Posts by G_Waddell

No worries remember to mark as solved!

Hi
Have you tried running the update statement directly inside your database?
Also, that statement is not being dynamically changed ie. you are always setting the equipage field to Skipper on the record(s) where the Num field = 10

Hi,

Your SELECT statement that is run in the command is only selecting quantity and rate so the datareader will only have quantity and rate fields.

Hi,
Try debug with break points and step through it, paying particular attention to the connection string values. Also, check your access Database isn't open elsewhere or locked - This could prevent the update from happening

As daniel955 says, you should watch that capitalisation, "mm" is actually minutes in VB.NET as in "hh:mm:ss" e.g. "28/10/2013 11:30:44" is "dd/MM/yyyy hh:mm:ss" but if you had converted this date to "dd/mm/yyyy" you would get this string "28/30/2013" (28th day, 30 minutes, year 2013)

Hi,

If you used a DataAdaptor to supply the datasource, you can specify the Insert Update and Delete Commands then apply the Update method to push the changes over to the database. DataAdaptor Updates To add a new row to your DataGridView you can either pass the "default" values in as an array:

dim DGVRow as New DataGridViewRow (Cell1value, Cell2value, .... LastCellValue)

MyDataGridView.Rows.Add(DGVRow)

OR Clone an existing row already in the grid.

Dim DGVRow as DataGridViewRow 

DGVRow = MyDataGridView.Rows(0).Clone

MyDataGridView.Rows.Add(DGVRow)

Recently taken up Judo (2-3 months) Going for my yellow belt in Early November.

I also attempt to play golf and have been for years...

Sorry,

I should mentioned at the bottom of the combobox column link I sent there is an example of adding one to a datagrid and populating it with values while linked to a Datasource.

Hi
The article link I gave you should cover using autocomplete.

This link is the MSDN site link to the ComboboxCell class

This is for the ComboBoxColumn Class

Sorry,

I based my answer on an example from some code I wrote not on your specifc code.

If you look at my code entries you will see that GetNoOfVisits is a function that takes in a userID and returns the number of visits from the database using a stored procedure with an output parameter. I should have also passed in a sql connection to the function - my bad!

I'm then feeding the result of this function into another parameter on another query.

Oh nearly forgot you may have to set the autogeneratecolumns property to false to let you specify and work with the column types

Hi,

This Article is all about using the Autocomplete property in VB.NET. I'm not 100% sure about the normal DataGridViewTextBoxColumn but I do know the DataGridViewComboBoxColumn supports Autocomplete.

Hi
Does your Stored procedure use an Output Parameter? (Example below:)

CREATE PROCEDURE GETVISITCOUNT @UserID bigint, @NoVisits int OUTPUT 
AS
SELECT @NoVisits = COUNT(*) FROM Visits WHERE (UserID = @UserID)
...

Then in your code you would add the parameters but specify the NoVisits parameter is an output parameter:

Function GetNoOfVisits(byref UserID as integer, ByRef MyConn as SqlConnection) As integer
dim Visits as Integer = 0
Try
    'Set Up Command
    dim cmd as SqlCommand
    cmd.Connection = MyConn

    'You are executing a stored Procedure
    cmd.CommandType = CommandType.StoredProcedure

    'The Text will be the name of the Procedure
    cmd.CommandText = "GETVISITCOUNT"

    'Add Parameters
    cmd.Parameters.Add("@UserID", SqlDBType.BigInt)
    cmd.Parameters.Add("@NoVisits", SqlDBType.Int)

    'Specify Value of Input Parameter
    cmd.Parameters("@UserID").Value = UserID

    'Specify direction of output parameter (default is input)
    cmd.Parameters("@NoVisits").Direction = ParameterDirection.Output

    'Check Connection is open
    If MyConn.State <> ConnectionState.Open Then
        MyConn.Open() 'Open connection
    End if

    'Our output parameter will be populated with the data we need so no dataset required
    cmd.ExecuteNonQuery()

    Visits = cmd.Parameters("@NoVisits").Value

    return Visits
Catch 
    return 0
Finally
    cmd.dispose()

    if MyConn.State <> ConnectionState.Closed then
        MyConn.Close()
    end if

end try

You could then input this result into your next query as a parameter...

cmd2.Parameters.Add("@NoVisits", SqlDbType.Int)
cmd2.Parameters("@NoVisits").Value = GetNoOfVisits(MyUserID)

Hi,

Sorry again it's my lack of knowledge on Access syntax...These would work in SQL server

Basically DATEADD function adds a given date interval to a date. In this case I'm adding -1 Month to the current date (GETDATE is a SQL Server function to get the current Date) in otherwords, I'm getting last months date (remember if your in January the last month was the December before).

OK now to Mileage % 10000 =0 this is the equivalent of the VB Mileage Mod 10000 =0 in otherwords the modulus of mileage/ 10000 =0 i.e. the Mileage is an exact multiple of 10000. I just realised as I type this, I should also have put AND Mileage > 0 in as 0 divided by any number will always give zero.

So what you want is something like:

SELECT DISTINCT * FROM Vehicles  
WHERE (LastServiced = DATEADD(Month,-1, GETDATE())) 
OR
(Mileage % 10000 = 0 AND  Mileage > 0)

That will get all vehicles that were last serviced 1 month ago or that have a Mileage that is a multiple of 10,000

Hi,

You need to think about the two separate cases you have just outlined. You have to forgive me but I'm not 100% sure of Access syntax

Case 1 - one month or more since last serviced, the sql would be something like: WHERE (LastServiced <= DATEADD(Month, -1, GETDATE())

Case 2 - Mileage trigger, I'm not sure if you want a set of predefined triggers i.e. 20,000 30,000 40,000 etc or something like every 10000 miles : WHERE(Mileage IN (20000, 30000, 40000, 50000)) or WHERE(Mileage % 10000 = 0)

When you have settled on exactly what you want then you would combine them with an OR operator (You would also add a DISTINCT clause to the Query if you did not want a vehicle that satisfied both clauses appearing twice) e.g. :

SELECT DISTINCT * FROM Vehicles  
WHERE (LastServiced = DATEADD(Month,-1, GETDATE())) 
OR
(Mileage % 10000 = 0)

Hi,
The combobox will take objects rather than just strings as it's items.

I tend to make a custom class I call it dataitem and give it a string property and a data property. You then can override the ToString to return your String value:

Class DataItem

Private _Data as object
Private _Text as string

Public Property Data as object
Get()
    Return _Data
End Get
Set (byVal value as object)
    _Data = value
End Set
End Property

Public Property Text
Get()
    Return _Text
End Get
Set (byVal value as string)
    _Text = value
End Set
End Property

Public Overrides Function ToString() As String
    Return _Text
End Function

Sub New (Optional byVal Data as Object = nothing, Optional ByVal Text as String ="")
    _Text = Text
    _Data = Data
End Sub

End Class

Now you can add your combobox items like this:

MyComboxbox.items.add( new DataItem(1,"Male"))
MyComboxbox.items.add( new DataItem(2, "Female"))

Then when you want to read the value:

dim DI as DataItem
dim MySex as integer
DI = MyCombobox.SelectedItem

MySex = cint(DI.Data)

Hi,

Your going to have to loop through all the subfolders from your starting point all the way down to where you can not find any sub folders trouble is your going to have to keep doing this for each folder you find and each of it's sub folders...

If you want to be able to give your users the option of selecting a folder why not use the FolderBrowserDialog and let them navigate?

Hi
You should look up the OLEDBConnection Class to find out how to run SQL against a Database through OLEDB (as opposed to SQL Server).

To connect to the Access database through the OLEDBConnection Class, you will need a connection string to connect to Access

This should get you started - there are loads of examples of code both in MSDN and other VB.NET sites as well as examples and snippits on this very forum.

Hi,

Try modifying the SQL statement from Select Dept From SoldItems Where SalesDate >= @StartDate And SalesDate <= @EndDate to Select Distinct Dept From SoldItems Where SalesDate >= @StartDate And SalesDate <= @EndDate

The DISTINCT command does precisely that i.e. returns only DISTINCT results.

Sorry, if you want to use the override a behaviour of the base class on your custom class you would do it like this:

Public class DataItem

private _Data as Object
private _Display as string

Public Property Data As Object
     Get()
        return _Data
    end Get 
    Set (byref value as Object)
        _Data = Value
    end Set
End Property
Public Property Display as String
    Get() 
        return _Display
    end Get
    Set (byref value as Object)
    _Display = value
    end Set
End Property
Public Overrides Function ToString()
    Return _Display
End Function
Public Sub New (Optional ByVal Key as String ="", Optional ByVal Item as Object = nothing)
    _Data = Item
    _Display = Key
End Sub
End Class

I was going to say you could use the AddHandler Statement BUT if you follow the example given by tinstaafi you don't need to - the custom class is always set to read only and always has a white background.

I'll give you an example of using addhandler on a control if you want, but you could also just append Handles Event to the sub.

'Example of setting a sub routine to handle an event with addhandler:

sub HandleMyEvent1(byval sender as object, byval e as system.eventArgs)
    msgbox("You clicked me")
end sub

dim mycustomControl as new CustomControl 

me.controls.add(mycustomControl)

addhandler mycustomControl.OnClick, AddressOf HandleMyEvent1

'Example of setting sub routine with handles
dim MyControl2 as New CustomControl
me.controls.add(MyControl2)

sub HandleMyEvent2(byval sender as object, byval e as system.eventArgs) Handles MyControl2.MouseOver
    msgbox ("Mouse Over me")
End Sub

Hi,

Looking at you code, if the form shows as a dialog then your interface and form that calls the code are working... The code inside your form, handles a keypress on txtbox1 inside the form. So when someone enters text into the form does it handle it - what happens? Have you tried putting a msgbox inside the form code to see if it is firing?

Hi,

The fact he sent you an updated exe to accomplish this suggests he did hard code it somewhere.

Hi,

It'd be easier to figure out if we can see some of your code - where you make the calls etc between the projects.

Hi,

You will need to output the new Order Number as an output parameter or return in your Stored Procedure. You should drop the datareader too and use cmd.ExecuteNonQuery as you are not returning a data result set.

Tweek your stored procedure something like as follows:

CREATE PROCEDURE Update_OrderNo @OrdNo BIGINT OUTPUT

INSERT INTO ORDRNUMBERREC(OrderNo,UserId,CreateDate)
SELECT MAX(OrderNo)+1,'system',GETDATE() FROM ORDRNUMBERRECWITH (TABLOCKX)

--CHECK IT HAS INSERTED CORRECTLY
IF @@ERROR =0 
BEGIN
    SELECT @OrdNo = MAX(OrderNo) FROM ORDRNUMBERRECWITH 
END
--IN Case of Failure
IF @@ERROR <> 0 
BEGIN 
    -- Log the error - use a better message than I did :)
    RAISERROR('Awhhh Crap',16,1) WITH LOG   
    SELECT @OrdNo = 0
END

Now for you VB Code:

cmd = New SqlCommand
cmd.CommandText = "Update_OrderNo"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
'Add your parameter
cmd.Parameters.Add("@OrdNo", SqlDBType.BigInt)
'specify it is an output parameter
cmd.Parameters("@OrdNo").Direction = ParameterDirection.Output
'open connection if necessary
If con.State.ToString <> "Open" then
    con.open
end if
'Run procedure
cmd.ExecuteNonQuery
'Get Result
ordrno = cmd.Paramters("@OrdNo").value

Hope this helps

Hi,

You've concatenated the fields in your query and you want to split them back out again?

You could try using the split function on the space character:

dim Names() as String
....

Names = dgvProfessor_bs.Rows(e.Rowindex).Cells(1).Value.Split(New Char() {" "c})

textboxFirstName.Text = Replace(Replace(Names(0), ",",""),".","")
TextboxLastName.Text = Replace(Replace(Names(1),",",""),".","")
TextboxMiddle.Text = Replace(Replace(Names(2),",",""),".","")

Hi,
Your code to get the sections looks wrong, you should be passing in the whole rich text box text not data1 and I'm not sure you are calculating the point correctly i.e. sLeft = left(rtb.text, rtb.selectionstart-1) would get you the text in the rich text box just before the selection. sRight = mid(rtb.text,rtb.SelectionStart) would get you the text in the textbox from the selection start to the end of the textbox.

this method will return the contents of a specific field in the curtrent record

This is in a comment on one of the functions in "your" class I suggest starting here...

Hi
Are you using a common networked database for all users? Or is it that you are deploying the database with the program i.e. one database per user?

Hi
your databinding is wrong, the dataset will contain a table with the data retrieved from the query run in your data adapter. (The idea is that you can store several different query results in a single dataset and even link them with relationships - think of it as a local database)

In fact you did have the datagrid binding to the correct datatable at the end of your second post:

dgvPartDescription.DataSource = ds.Tables(0)

You may have just needed to refresh it....

dgvPartDescription.Refresh

Sorry,
but a web based application and a desktop application are two different things. Do you mean you want the clients to be able to install the application from a web site? or do you mean create a web enabled / web based version of your application?

Hi,

You'll want to look at relation databases and design - quite a subject in it own right but in a nut shell: Suppose you have a table of doctors, a table patients and a table of visits.

Your doctor table contains all the data on your doctors but what if for example, you have two doctors named "John Smith" so you put a column in the doctor table that will uniquely identify each doctors record lets call it doctorID (as your using SQL Server make it an integer and enable Identity on the field,) now for each new doctor record entered the table will automatically populate the doctorID field with the next number in sequence. You can now mark this field as a PRIMARY KEY as it will be unique to each doctor i.e. there will be only one record with a DoctorID of 1.

Now for your patients you would do the same thing, except we'll call it PatientNumber. So You have two tables Patient and Doctor both with Primary Keys.

Now Patients Visit doctors and doctors take visits so your Visit table will link each patient and doctor to the history of the visit. First of all give Visit a primary Key just like you did to the others (VisitNumber?) Then you want to link the visit to the doctor - so you need a field in the visit table that links to the correct Doctor.... Now what do we have in the Doctors table that is …

Hi,

I haven't a clue where to start on that one, maybe you should post under the Hardware & Software forum - it sound like the issue you have is hardware related i.e. how do you read which pin is live

Another tip, you will wish to avoid SQL Injection Attacks where a user enters a damaging piece of SQL into an input that is then run on your SQL database as the input is processed by SQL Server.

• Try to use and run stored procedures with parameters on the database rather than building queries on the fly
• If you have to build a query on the fly, then use parameters
• Connect to the database via a single SQL user that only has permission to execute the stored procedures you will be running.
• Try and stop the damage in the first place by limiting input into textboxes etc i.e. maximum lengths, filtering input.

Hi,
For retrieving Data from your database:
SQLDataAdaptor
SQLDataReader

For running commands on the database such as Inserts and Updates:
SQLCommand

For Connecting to a database:
Connectionstrings.com

Show us what code you have so far and where it is falling over/ you are stuck...

First are you sure there is definately a table named Personal_Details in your database?

If so try using [Personal_Details] instead of just Personal_Details access may not like the table name not being bracketed off.

Also, (because I know others will point this out,) it is a bit of a security risk not to use parameterised queries when building your SQL string - you run the risk of a SQL injection attack.

Hi,
You should just in case also convert your search string to lower to ensure both the search string and the string being searched are in the same case.

Why not use Instr?

IF Instr(StringToSearch, lcase(trim(Textbox1.Text))) <> 0 Then

You added "ON DELETE CASCADE" to your statement. You should remove this from the Customer_Sample table and apply it to the Sample table.

If Random guys can just show up take your dll and decompile it then a login page is the least of your security issues on that server! If you're that worried you can use an obfuscation program. But frankly if you have a secure server they shouldn't be able to even get to that stage. What if they get in and hijack your session? or use cross site scripting? You are only as secure as the system you design and build on. ASP.NET is just as secure as PHP

So you're saying an uncomplied php file is more secure than a complied .Net program?

Hi

Why use PHP? Why not VB.NET > MySQL?

Hi you have added the panel twice once to the Main panel and once to the form:

 'Adding to main panel
 pnlMainPanel.Controls.Add(pnlOrderLine)
 pnlMainPanel.Visible = True
 'Adding to Form i.e. me
 Me.Controls.Add(pnlOrderLine)

Hi,

I'm seeing lots of text values being passed in but no string delimiters e.g.

'Like you have
sql ="INSERT INTO MYtable(MyField, MyField2) VALUES(" &MyValue &", " &MyValue2 &")" 

'Example where MyField is a text (string type Field) and MyField2 isnumeric -spot the difference
sql ="INSERT INTO MyTable(MyField, MyField2) VALUES('" &MyValue &"', " &MyValue2 &")"

Also, it should be pointed out that you should really try to use (at the least,) parameterised queries to avoid SQL injection attacks (where nasty people "sneak" SQL commands into your inputs to delete tables etc.)

This link, Working with OLEDB parameters will show you how to use parameters with OLEDB - As you are using a Text command though you can not name your parameters so you must add them in the correct order e.g.

'We don't need to use delimiter as parameters will specifiy their Type
SQL ="INSERT INTO Employee(FirstName, LastName, StartName) VALUES(?, ?, ?)"

dim Cmd as new oleDBCommand(con)

With Cmd
    .CommandType = CommandType.Text
    .Text = sql
    'Now I will demonstrate adding the parameters
    'Using .AddwithValue this will use the value to decide the parameter Type
    .parameters.AddWithValue("@FirstName",Trim(txtFirstName.Text))
    .parameters.AddWithValue("@LastName", Trim(TxtLastName.Text))
    'Now I'll use .Add to specify the type exactly in this case convert to date from a string
    .parameters.add("@StartDate", OleDbType.Date).Value = Trim(TxtStartDate.Text)
End With

Cmd.Execute()

It is because you were using an if clause which expects something it can evaluate as true or false

if TRUE Then
    'do this
Else
    'do that
End if

So you where returning a string which the code cannot evaluate as either TRUE or FALSE. What I did was to change the code to basically say:
IF the result of the ScalarQueryCardID is not equal to (<>) an Empty string ("") then

I suspect the code is don't a boolean conversion on the numeric value i.e. 0 = False 1 = true why that would be I don't know - do you have Option Explicit enabled?

Hi
I suspect it could be this line:
If Me.TbmembersTableAdapter.ScalarQueryCardID(cardid) Then
You say the .ScalarQueryCardID is just returning the CardID as a string? Then I think you should be using:
If Me.TbmembersTableAdapter.ScalarQueryCardID(cardid) <> "" Then

Without knowing more about .ScalarQueryCardID I can't really comment.

Hi,

I think you are applying the cascade delete to the wrong table.

You are altering the table Sample Customer to include a Foreign Key (Sample_Note_No) linked to Sample.Sample_Note_No but you are then saying if the record in Sample Customer is deleted, delete the corresponding record in Sample.

Therefore SQL is stopping you because if you did this you would delete the record in Sample and potentially leave orphaned records in the Sample Customer table (assuming a One to Many relationship, which SQL server is.)

I think you want to apply the cascade to the Sample Table so if you delete the record there you delete any child records in Sample Customers.

Hi,

Looking at your code, I can only see two possible reasons for a syntax error:

1. You are passing strings in for every field you are updating, are all the fields in the database definately string types?
2. txtUser.text is either blank or has a value that doesn't match to a record in the table. i.e. there is no matching record in the table to update.

There could be something else going on but thats all I can see so far. As Reverend Jim says bedugg the code and do a watch on sqlupdate. You could try copying the sql produced in sqlupdate into your access DB querybuilder (in SQL mode,) and see if it highlights any syntax errors.

Hi,

Does this not work?

Dim mag As New Magento.ecommerce
Dim Stockcodes As New List(Of magento.clsStockcode) 
Stockcodes = mag.ReturnNewStockodes("admin", "password")

If not, try replacing magento.clsStockcode with mag.clsStockcode

Hi,

If it returns a list, you should be able to navigate through it:

Dim Stockcodes As New List(Of magento.clsStockcode)
dim Stockcode as magento.clsStockcode
dim code as string

'Get your stockcodes
If Stockcodes.items.count > 0 then 'I think it's items.count, could just be .count
    For each Stockcode in StockCodes
        'work with the stockcode e.g.
        code = Stockcode._StockCode
    next
end if