VB.NET & SQL server - where clause

[amber]

Hello
I am using the MS Data Access Application Block in VB.NET, and I'm having no problems with my queries that call one table with no parameters
I can't figure out how to do the following
I want to pull data from 1 tables, with a variable as a parameter
The code I'm using (that isn't working) is

lBoxCP.DataSource = Nothin
lBoxCP.Items.Clear(
lBoxCP.Refresh(
Dim CI as string = "TDT_CUTTING_PERMIT.STR_CUTTING_PERMIT
Dim ConStr As String = "workstation id=LAPTOPAMBER;packet size=4096;integrated security=SSPI;initial catalog=fsAbitibi;persist security info=False
Dim dssCP As DataSet = SqlHelper.ExecuteDataset(ConStr, CommandType.Text,
"SELECT ID_CUTTING_PERMIT, STR_CUTTING_PERMIT, STR_FOREST_DISTRICT, STR_FOREST_REGION, STR_TSA, STR_LICENSEE, STR_LICENSE_NO, STR_OPERATING_AREA, STR_TIMBER_MARK, STR_CATEGORY, DTM_FORECAST, STR_EMPLOYEE, DTM_COMPLETE, STR_SIGN_OFF_BY, DTM_SIGN_OFF, STR_PEER_REVIEW, DTM_PEER_REVIEW, STR_SIGN_OFF_STATUS, STR_SIGN_OFF_COM FROM TDT_CUTTING_PERMIT WHERE STR_CUTTING_PERMIT = ", CI

lBoxCP.DataSource = dssCP.Tables(0
lBoxCP.DisplayMember = "STR_CUTTING_PERMIT

Does my problem lie with the Where clause?

Thanks in advance
Ambe

[CJ Taylor]

I don't have a lot of experience with the data applicaiton block but your
sql statement in general is kinda weird..

You don't ever seem to assign a variable area (such as String.Format uses
like {0})

Even when you do... its always a true value... you might want to read up
more on the data appilcation block and how to do variables in the SqlHelper.

-CJ

"amber" <an*******@discussions.microsoft.com> wrote in message
news:75**********************************@microsof t.com...

Hello,
I am using the MS Data Access Application Block in VB.NET, and I'm having no problems with my queries that call one table with no parameters. I can't figure out how to do the following:
I want to pull data from 1 tables, with a variable as a parameter.
The code I'm using (that isn't working) is:

lBoxCP.DataSource = Nothing
lBoxCP.Items.Clear()
lBoxCP.Refresh()
Dim CI as string = "TDT_CUTTING_PERMIT.STR_CUTTING_PERMIT"
Dim ConStr As String = "workstation id=LAPTOPAMBER;packet size=4096;integrated security=SSPI;initial catalog=fsAbitibi;persist
security info=False" Dim dssCP As DataSet = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, _ "SELECT ID_CUTTING_PERMIT, STR_CUTTING_PERMIT, STR_FOREST_DISTRICT, STR_FOREST_REGION, STR_TSA, STR_LICENSEE,
STR_LICENSE_NO, STR_OPERATING_AREA, STR_TIMBER_MARK, STR_CATEGORY,
DTM_FORECAST, STR_EMPLOYEE, DTM_COMPLETE, STR_SIGN_OFF_BY, DTM_SIGN_OFF,
STR_PEER_REVIEW, DTM_PEER_REVIEW, STR_SIGN_OFF_STATUS, STR_SIGN_OFF_COM FROM
TDT_CUTTING_PERMIT WHERE STR_CUTTING_PERMIT = ", CI)
lBoxCP.DataSource = dssCP.Tables(0)
lBoxCP.DisplayMember = "STR_CUTTING_PERMIT"

Does my problem lie with the Where clause??

Thanks in advance,
Amber

[Cor]

Hi Amber,

In addition to CJ

Dim CI as string = "TDT_CUTTING_PERMIT.STR_CUTTING_PERMIT"

TDT_CUTTING_PERMIT WHERE STR_CUTTING_PERMIT = ", CI)

I did not use the application blocks either,

But this should be connection, command, selectstring, oledbparameter.

You have mixed it a little bit up.

I think the the most simple is (to start) to change the permit in either (if
it is a string or a integer) in
CUTTING_PERMIT = '" & CI & "'") 'string
or

CUTTING_PERMIT = " & CI.tostring ) 'integer

And than place in CI the right value, I think that what now is in it is
absolute wrong.

I hope this helps?

Cor

Cor

[amber]

I was very unclear on wording my question and problem

What I want to do is base my SQL query on a VB.net text box. so, WHERE STR_CUTTING_PERMIT = txtCPmain.tex
nothing I type seems to work..
In my original example of my code, I made an error, I had made my variable equal to the wrong thing..
I said
Dim CI as string = "TDT_CUTTING_PERMIT.STR_CUTTING_PERMIT
but it would actually be
dim CI as string = txtCPmain.tex

Does this make it a bit clearer

Sorry
Ambe

[Cor]

Amber,

Yes and than try it as I said with at the end

CUTTING_PERMIT = '" & CI & "'")

I hope this works,

Cor

[CG]

Surely the last line of the SQL statement should be
...STR_SIGN_OFF_COM FROM TDT_CUTTING_PERMIT WHERE STR_CUTTING_PERMIT =
'" & CI & "'")

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Brian Henry]

use the command object's parameters.. they make life easier on you

"amber" <an*******@discussions.microsoft.com> wrote in message
news:D8**********************************@microsof t.com...

I was very unclear on wording my question and problem.

What I want to do is base my SQL query on a VB.net text box. so, WHERE STR_CUTTING_PERMIT = txtCPmain.text nothing I type seems to work...
In my original example of my code, I made an error, I had made my variable equal to the wrong thing... I said
Dim CI as string = "TDT_CUTTING_PERMIT.STR_CUTTING_PERMIT"
but it would actually be
dim CI as string = txtCPmain.text

Does this make it a bit clearer?

Sorry.
Amber

[Stephen Muecke]

amber,

Something to consider (from Jeff Levinson book, Building Client/Server
Applications in VB.NET)
Using a TextBox to create a SQL Query is subject to an SQL injection attack.
eg, the following will retrieve all rows from the Books table where the
title matches the text entered by the user
SELECT * FROM dbo.Books WHERE Title = '" & txtTitle.Text & "'
a malicious user could enter the following in the TextBox
' DROP TABLE Books --
The resulting SQL statement would be
SELECT * FROM dbo.Books WHERE Title = '' DROP TABLE Books --
which would result in your table being deleted!
Use stored procedures if possible

Stephen
"amber" <an*******@discussions.microsoft.com> wrote in message
news:D8**********************************@microsof t.com...

I was very unclear on wording my question and problem.

What I want to do is base my SQL query on a VB.net text box. so, WHERE STR_CUTTING_PERMIT = txtCPmain.text nothing I type seems to work...
In my original example of my code, I made an error, I had made my variable equal to the wrong thing... I said
Dim CI as string = "TDT_CUTTING_PERMIT.STR_CUTTING_PERMIT"
but it would actually be
dim CI as string = txtCPmain.text

Does this make it a bit clearer?

Sorry.
Amber