Help with Database statement
Hey, i require some help with a database. Ive tried to explain what im trying to do in the code section below.
Basically im trying to write an insert/search statement to the database. So far i have this.
Code:
objCommand.CommandText = "INSERT INTO ORDER1 (CardNumber, IPAddress, IpMatch) VALUES (@CardNumber, @IpAddress, @IpMatch);"
objCommand.Parameters.AddWithValue("@CardNumber", cardnumber.text)
objCommand.Parameters.AddWithValue("@IpAddress", ipaddress.text)
objCommand.Parameters.AddWithValue("@IpMatch", ipmatch.checked)
Basically i need to check the database first, to determine whether the creditcardnumber is UNIQUE with the IP address. If a CreditCard/IP exists and a different CreditCard number is used with the same IP addresses, IPMatch should be unchecked. So before i insert the data, i need to check that the Card Number is unique or uses the same IP address as previously used. See below for the table.
Code:
TABLE - [ORDER1]
CARDNUMBER --- IPADDRESS --- IPMATCH (CHECKBOX)
0000000000000000 --- 1.1.1.1 --- checked
1111111111111111 --- 1.1.1.1 --- unCHECKED
0000000000000000 --- 1.1.1.1 --- checked
Check whether the 'CARDNUMBER' is unique for the 'IPADDRESS'
Only first Cardnumber is valid.
IE if 0000... is used again, it will be CHECKED
Any other card number with that IP should be unchecked.
Hopefully you can understand what im trying to do.
Thanks for looking! :thumb:
Re: Help with Database statement
First you must declare a constraint that define the credit card and ip are unique... just in case...
Then you can check with a query if the ip exists and if it exists it returns the credit cards related with that ip (with a reader), then if you have rows in the reader, you'll know that it's unchecked, if no rows are returned then it's checked...
Something like "SELECT CARDNUMBER FROM ORDER1 WHERE IPADDRESS = @ip"
I think this do the job..
Re: Help with Database statement
Thanks for your input. :thumb:
Ill see how i get on later tonight.
Re: Help with Database statement
im getting an error. data type mismatch. im not sure how to fix it exactly.
Code:
Dim rows as Integer
LabelSuccess3.Text = "Card Is Unique"
Card_IP_Unique.Checked = True
objCommand.CommandText = "Update [All] SET Card_IP_Unique = @Card_IP_Unique WHERE IpAddress = @IpAddress"
objCommand.Parameters.AddWithValue("@IpAddress", Me.ipaddress.Text)
objCommand.Parameters.AddWithValue("@Card_IP_Unique", Me.Card_IP_Unique.Checked)
rows = objCommand.ExecuteNonQuery
If rows = 1 Then
MessageBox.Show("Record Updated")
Else
MessageBox.Show("Could not execute query")
End If
Re: Help with Database statement
The error is raised because of this line:
VB.NET Code:
objCommand.Parameters.AddWithValue("@Card_IP_Unique", Me.Card_IP_Unique.Checked)
I don't know what type of database that you're using, but if the field expects a boolean value, try to send 1 for true and 0 for false...
Re: Help with Database statement
hey. i did that. didnt help. i found out its this code.
Code:
objCommand.Parameters.AddWithValue("@IpAddress", Me.ipaddress.Text)
if ipaddress.text contains something like 1.1.1.1 ; throws the error
if i use 1111, it works.
the ip is just "text" in the database, so i dont understand. Its passing as a string im assuming, so why is it a datatype error.
Re: Help with Database statement
Strange...
Are you sure that the field it's text?
Try to put IpAddress Like @IpAddress instead IpAddress = @IpAddress...
What db are you using?
Re: Help with Database statement
im using access.
i tried
card number = 5
ip = 5
works.
card number = 5
ip = 5.5
works.
card number = 5
ip = 5.5.5
throws a datatype mismatch error and goes to this line below.
Code:
objCommand.ExecuteNonQuery()
Re: Help with Database statement
Wierd anyway. I've fixed the error. just been messing about trial and error and now its working.
Code:
objCommand.CommandText = "Update [All] SET Card_IP_Unique = '" & Card_IP_Unique & "' WHERE IpAddress = @IpAddress AND CardNumber = '" & LabelSuccess2.Text & "' "
objCommand.Parameters.AddWithValue("@IpAddress", ipaddress.Text)
'objCommand.Parameters.AddWithValue("@Card_IP_Unique", Card_IP_Unique)
objCommand.ExecuteNonQuery()
Thanks for looking and your help