Help with Database statement

[vb_2008]

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.

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.

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!

[mickey_pt]

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..

[vb_2008]

Thanks for your input.

Ill see how i get on later tonight.

[vb_2008]

im getting an error. data type mismatch. im not sure how to fix it exactly.

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

[mickey_pt]

The error is raised because of this line:

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...

[vb_2008]

hey. i did that. didnt help. i found out its this 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.

[mickey_pt]

Strange...

Are you sure that the field it's text?
Try to put IpAddress Like @IpAddress instead IpAddress = @IpAddress...

What db are you using?

[vb_2008]

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.

objCommand.ExecuteNonQuery()

[vb_2008]

Wierd anyway. I've fixed the error. just been messing about trial and error and now its working.

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