Textbox and SQL query

[mentor714]

I just started learning ASP.NET (newb) and would like to use it to
connect to an ACCESS Db. Im using WebMatrix to start out. I created a
TextBox and Button so a user can query the Db.


Sub btnSearch_Click(sender As Object, e As EventArgs)

Dim ConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=E:\PCMS.mdb"

Dim CommandText As String

CommandText = "SELECT [Module].* FROM
[Module] WHERE [Module].ModuleName= " +
"'TextBox1.Text'"


Dim myConnection As New
OleDbConnection(ConnectionString)
Dim myCommand As New OleDbDataAdapter(CommandText,
myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

DataGrid1.DataSource = ds
DataGrid1.DataBind()

End Sub



My question is: Why wont a simple concat from the Textbox work when
completing the SQL statement?

Also I created a seperate page for adding records to the database.
All the information will be read from textboxes. Am I on the correct
track?
[code:1:a653d68270]
Sub btnSubmit_Click(sender As Object, e As EventArgs)

Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=E:\PCMS.mdb"

Dim dbConnection As New OleDbConnection(connectionString)
dbConnection.Open()

Dim commandString As String = "INSERT INTO Module
(ModuleName, ModuleType, PartOf, Purpose, Designer, Date,
ModuleBody) " & _
"Values(@ModName, @ModType, @PartOf, @Purpose, @Designer,
@Date, @ModBody)"

Dim dbCommand As New OleDbCommand(commandString,
dbConnection)

Dim ModNameParam As New OleDbParameter("@ModName",
OleDbType.VarChar, 50)
ModNameParam.Value = txtModuleName.Text
dbCommand.Parameters.Add(ModNameParam)

Dim ModTypeParam As New OleDbParameter("@ModType",
OleDbType.VarChar, 50)
ModTypeParam.Value = txtModuleType.Text
dbCommand.Parameters.Add(ModTypeParam)

' the rest of the variables omitted in order to save space for post


Dim ModBodyParam As New OleDbParameter("@ModBody",
OleDbType.VarChar, 100)
ModBodyParam.Value = txtModuleBody.Text
dbCommand.Parameters.Add(ModBodyParam)

dbCommand.ExecuteNonQuery()

dbConnection.Close()

End Sub

[/code:1:a653d68270]

Thank you very much in advance...because I cant get my code to work

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

 

[Hans Kesting]

I just started learning ASP.NET (newb) and would like to use it to
connect to an ACCESS Db. Im using WebMatrix to start out. I created a
TextBox and Button so a user can query the Db.


Sub btnSearch_Click(sender As Object, e As EventArgs)

Dim ConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=E:\PCMS.mdb"

Dim CommandText As String

CommandText = "SELECT [Module].* FROM
[Module] WHERE [Module].ModuleName= " +
"'TextBox1.Text'"

Here you are filtering for the string "TextBox1.Value", NOT on
the value that is in the textbox!
Move the quotes:
" .... ModuleName = ' " + TextBox1.Value + " ' "
(note: extra spaces added around the ' for clarity!)
It would be even better to use parameters.

Dim myConnection As New
OleDbConnection(ConnectionString)
Dim myCommand As New OleDbDataAdapter(CommandText,
myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

DataGrid1.DataSource = ds
DataGrid1.DataBind()

End Sub



My question is: Why wont a simple concat from the Textbox work when
completing the SQL statement?

Also I created a seperate page for adding records to the database.
All the information will be read from textboxes. Am I on the correct
track?
[code:1:a653d68270]
Sub btnSubmit_Click(sender As Object, e As EventArgs)

Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=E:\PCMS.mdb"

Dim dbConnection As New OleDbConnection(connectionString)
dbConnection.Open()

Dim commandString As String = "INSERT INTO Module
(ModuleName, ModuleType, PartOf, Purpose, Designer, Date,
ModuleBody) " & _
"Values(@ModName, @ModType, @PartOf, @Purpose, @Designer,
@Date, @ModBody)"

You are using parameters, excellent!

Dim dbCommand As New OleDbCommand(commandString,
dbConnection)

Dim ModNameParam As New OleDbParameter("@ModName",
OleDbType.VarChar, 50)
ModNameParam.Value = txtModuleName.Text
dbCommand.Parameters.Add(ModNameParam)

Dim ModTypeParam As New OleDbParameter("@ModType",
OleDbType.VarChar, 50)
ModTypeParam.Value = txtModuleType.Text
dbCommand.Parameters.Add(ModTypeParam)

You assigning the value in the textbox (I assume txtModuleType is a TextBox).
That is correct.

' the rest of the variables omitted in order to save space for post


Dim ModBodyParam As New OleDbParameter("@ModBody",
OleDbType.VarChar, 100)
ModBodyParam.Value = txtModuleBody.Text
dbCommand.Parameters.Add(ModBodyParam)

dbCommand.ExecuteNonQuery()

dbConnection.Close()

End Sub

[/code:1:a653d68270]

Thank you very much in advance...because I cant get my code to work

"it doesn't work" does not give much information. You get more help if you
provide details:
- does it compile? (if not, what errors?)
- are there runtime errors? (what errors?)
- was there unexpected behavior? (what did you expect, what really happened?)

Hans Kesting

 

[mentor714]

Hans Kesting,

For the First problem:

CommandText = "SELECT [Module].* FROM
[Module] WHERE [Module].ModuleName= " &
" ' TextBox1.Text ' "

Can you give me an example on how to use parameters instead.

For the second problem, I put brackets around the table (i.e. INSERT
INTO [Module]) and it worked. Sorry about not being more clear.
A question about parameters. When i define a parameter like
[code:1:2825c7fafc]
Dim ModTypeParam As New OleDbParameter("@ModType",
OleDbType.VarChar, 50)
ModTypeParam.Value = txtModuleType.Text
dbCommand.Parameters.Add(ModTypeParam)
[/code:1:2825c7fafc]
(these are textboxes), does the last parameter, 50, need to match
exaclty the number used in the Access DB? Or can it possiable be
less?

Thanks

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

 

[Guest]

I would say the first and second parameter are the most important.
Why would you NOT what to make the last parameter the same size as what is
in the database? Are you looking for your code to break??

 

[mentor714]

Tampa .NET Koderwrote:
I would say the first and second parameter are the most important.

Why would you NOT what to make the last parameter the same size as what is
in the database? Are you looking for your code to break??

I was just curious to see if maybe the value can be less. If the
Access DB says var char up to 100, and the SQl parameter declared is
less than 100, say 90, wouldn't that help to contribute in stopping
the user from entering a value far greater than is allowed. Im not
looking to break my code, but simply understand some of the rules.

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

 

[Hans Kesting]

Hans Kesting,

For the First problem:

CommandText = "SELECT [Module].* FROM
[Module] WHERE [Module].ModuleName= " &
" ' TextBox1.Text ' "

Can you give me an example on how to use parameters instead.

For the second problem, I put brackets around the table (i.e. INSERT
INTO [Module]) and it worked. Sorry about not being more clear.
A question about parameters. When i define a parameter like
[code:1:2825c7fafc]
Dim ModTypeParam As New OleDbParameter("@ModType",
OleDbType.VarChar, 50)
ModTypeParam.Value = txtModuleType.Text
dbCommand.Parameters.Add(ModTypeParam)
[/code:1:2825c7fafc]
(these are textboxes), does the last parameter, 50, need to match
exaclty the number used in the Access DB? Or can it possiable be
less?

Thanks

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

For the size I always use the size of the *value*, not the maximum length
available for that field.

Hans Kesting