Textbox and SQL query

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(@dotnet.itags.org.ModName, @dotnet.itags.org.ModType, @dotnet.itags.org.PartOf, @dotnet.itags.org.Purpose, @dotnet.itags.org.Designer,
@dotnet.itags.org.Date, @dotnet.itags.org.ModBody)"
Dim dbCommand As New OleDbCommand(commandString,
dbConnection)
Dim ModNameParam As New OleDbParameter("@dotnet.itags.org.ModName",
OleDbType.VarChar, 50)
ModNameParam.Value = txtModuleName.Text
dbCommand.Parameters.Add(ModNameParam)
Dim ModTypeParam As New OleDbParameter("@dotnet.itags.org.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("@dotnet.itags.org.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
*--*mentor714 wrote:
> 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
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
*--*
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" wrote:

> 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
> *--*
>
> 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
*--*
mentor714 wrote:
> 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