Trouble updating

I have a Access database with 2 text fields. I'm reading one field into a text box, changing the value in the textbox and attempting to update the database with the new value. The page loads fine with the correct value in the textbox, but I'm getting a error on the Update statement. Below is the code. Any suggestions?

Sub LoadInventory()
Connect()
Dim strQuery As String = "SELECT Type, Number FROM Inventory WHERE type = 'squares'"

Dim dbComm As New OLEDBCommand(strQuery, objConnection)
Dim reader As OLEDBDataReader = dbComm.ExecuteReader()

reader.Read()
txtAmount.Text = reader.GetString(1)

reader.Close()
Disconnect()
End Sub

Sub btnChange_Click(ByVal Sender As Object, ByVal E As EventArgs)
UpdateInventory()
LoadInventory()
End Sub

Private Sub UpdateInventory()
Dim strSQL As String = "UPDATE Inventory SET Number = " & txtAmount.Text & " WHERE Type = 'squares'"
Connect()
Dim dbComm As New OLEDBCommand(strSQL, objConnection)
dbComm.ExecuteNonQuery()
Disconnect()
End Subinstead of

Dim strSQL As String = "UPDATE Inventory SET Number = " & txtAmount.Text & " WHERE Type = 'squares'"

try this

Dim strSQL As String = "UPDATE Inventory SET Number = " & Convert.ToInt32(txtAmount.Text) & " WHERE Type = 'squares'"

Jeff
Jeff,

Thanks for your reply. That however didn't work. Could it be the way I have my access database setup?

Jeremy
Jeff,

Does it work when you try putting in a static value for txtAmount.Text such as this:

"UPDATE Inventory SET Number =1 WHERE Type='squares'"

If it does work like that, then try doing this:

Try
 Dim dbComm As New OLEDBCommand(strSQL, objConnection)
 dbComm.ExecuteNonQuery()
Catch dbException As Exception
 Response.Write(dbException.ToString())
End Try

Tell us what the exception is. Besides, it is always good practice anyway to put database queries inside a try statement - there are just way too many things that could go wrong to not catch those errors.

Aaron

trouble with passing date to stored procedure

Trying to create a app that will read through our log files. I am reading through a directory and for each file I loop line by line and split the log into an array by SPACE and then send the IP, name of the log file, referer URL, and datetime to a stored procedure (below). The following code just returns 0 as a result and is not true. I believe my issue is in the asp.net datetime vs the datetime format in my database.

The datetime sent to the SP is: 10/17/2004 12:00:19 AM
The datetime in the field DT_TM I am comparing to is: 2004-06-23 10:17:23.000

Just hoping someone has a good grasp on this datetime format issue since I am clueless at this point on what to do. Cause I was sending in without converting to a date and kept getting errors.

This section of code is a portion taken from my .aspx file

'----
Dim dtLogDateTime as DateTime
adoCon = New SqlConnection("Server=xx.xx.xx.xx;Database=dbname;Uid=user;Pwd=pwd")
cmdSQL = New SqlCommand("sc_add_referer", adoCon)
cmdSQL.CommandType = CommandType.StoredProcedure
'Input Parameters
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_IP", arrArray2(5))
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_LOG_FILE", arFile(arFile.Length -1))
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_REFERER", arrArray2(8))
dtLogDateTime = arrArray2(0) & " " & arrArray2(1)
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_DT_TM", dtLogDateTime)
'Output parameter
parmSQL = cmdSQL.Parameters.Add("ReturnValue", SqlDbType.Int)
parmSQL.Direction = ParameterDirection.ReturnValue
adoCon.Open()
cmdSQL.ExecuteNonQuery()
iReturnVal = cmdSQL.Parameters("ReturnValue").Value
If iReturnVal = -2 Then
Response.Write("<br>" & arrArray2(3) & " " & arrArray2(5))
End if
adoCon.Close()
'----

The following is the SQL Server stored procedure I am calling

CREATE PROCEDURE sc_add_referer
(
 @dotnet.itags.org.SENT_IP varchar(15),
 @dotnet.itags.org.SENT_LOG_FILE varchar(15),
 @dotnet.itags.org.SENT_REFERER text,
 @dotnet.itags.org.SENT_DT_TM datetime
)
 AS
DECLARE @dotnet.itags.org.ROW_CNT INT
DECLARE @dotnet.itags.org.ROW_ORDERID INT
DECLARE @dotnet.itags.org.ROW_DT_TM INT
DECLARE @dotnet.itags.org.SUB_CNT INT
--FIRST NEED TO FIND OUT IF IN MAIN TABLE
SELECT @dotnet.itags.org.ROW_CNT = count(*), @dotnet.itags.org.ROW_ORDERID = ORDER_ID
FROM sc_ip_tracking
WHERE IP = @dotnet.itags.org.SENT_IP
GROUP BY ORDER_ID
SELECT @dotnet.itags.org.SUB_CNT = count(*)
FROM sc_ip_tracking_referers
WHERE ORDER_ID = @dotnet.itags.org.ROW_ORDERID
AND LOG_FILE = @dotnet.itags.org.SENT_LOG_FILE
IF @dotnet.itags.org.ROW_CNT > 0
 -- IP FOUND SO SEE IF FOUND IN REFERER LOG
 IF @dotnet.itags.org.SUB_CNT > 0
 --ROW ROUND
 RETURN 0
 ELSE
 -- NO ROW FOUND SO NEED TO ADD INFO
 BEGIN
 SELECT @dotnet.itags.org.ROW_DT_TM = datediff(hour, @dotnet.itags.org.SENT_DT_TM, DT_TM) FROM sc_ip_tracking WHERE IP = @dotnet.itags.org.SENT_IP AND ORDER_ID = @dotnet.itags.org.ROW_ORDERID
 IF @dotnet.itags.org.ROW_DT_TM >= 0 AND @dotnet.itags.org.ROW_DT_TM <= 8
 BEGIN
 INSERT INTO sc_ip_tracking_referers (ORDER_ID, LOG_FILE, REFERER, LAST_CHANGE_DT)
 values (@dotnet.itags.org.ROW_ORDERID, @dotnet.itags.org.SENT_LOG_FILE, @dotnet.itags.org.SENT_REFERER, getdate())
 END
 RETURN -2
 END
ELSE
 RETURN 0
GO

Replace the "/" in the date with "-" and drop the " AM" at the end of the string. You should be good to go.