Help with SQL statement

[MikeL91]

This code will not work, it says unhandeled exception.

[red] Private Sub btnState_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnState.Click
Me.OleDbSelectCommand1.CommandText = "SELECT Name, [Company Name], Address, City, State, Zip, Telephone, PubID FROM Pub" & _
"lishers WHERE Name like " & cmbState.Text.ToString
Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
'
OleDbDataAdapter1.Fill(DataSet11)
OleDbDataAdapter2.Fill(DataSet11)
End Sub [/red]



This code works, it pulls everything in my table:

[green] Private Sub btnAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAll.Click
Me.OleDbSelectCommand1.CommandText = "SELECT Name, [Company Name], Address, City, State, Zip, Telephone, PubID FROM Pub" & _
"lishers"
Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
'
OleDbDataAdapter1.Fill(DataSet11)
OleDbDataAdapter2.Fill(DataSet11)
[/green]

 

[ThatRickGuy]

check the Where clause:

Me.OleDbSelectCommand1.CommandText = "SELECT Name, [Company Name], Address, City, State, Zip, Telephone, PubID FROM Pub" & _
        "lishers WHERE Name like '%" & cmbState.Text.ToString & "%'"

-Rick

----------------------

http://www.ringdev.com

 

[MikeL91]

no that still does not work. here is the exact error message:

[red]
An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll

Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
[/red]

 

[ThatRickGuy]

Not sure on that one. It sounds like some sort of data issue, but that shouldn't cause a problem with a select statement, unless the bad data is already in the table. You may have to go through the table manually and find the record the is violating the constraint.

-Rick

----------------------

http://www.ringdev.com

 

[MikeL91]

Thanks for your help anyway, I guess I'll keep playing with it. The Select * command works though, thasts whats wierd, so I don't suspect the data, but I am new to .net.

It should be in a stored proc. but im using access DB, so I thought this would be a way around that.

 

[ThatRickGuy]

access's syntax on the like statement might be slightly different. try switching the % signs with ? or * or something.

-Rick

----------------------

http://www.ringdev.com

 

[RiverGuy]

The first question is this: do you have any duplicate PubID's?

 

[ThatRickGuy]

Okay, popped open access, the like syntax should be as follows:

"WHERE Name like " & controlchars.Quote & "*" & cmbState.Text.ToString & "*" & controlchars.Quote

That should give you:
WHERE Name Like "*value*"

-Rick

----------------------

http://www.ringdev.com

 

[stevexff]

Is it a NULL problem? Not sure what happens with LIKE and nullable columns...

Try

WHERE Name is not null and Name like '%" & cmbState.Text.ToString & "%'"

and see what happens.