I am trying to use Insert Statement which is one of the field(format = Number) in Access = 0. My program does not understand data which is one of the field = 0. Can you help me to solve the problem.
I am appreciate for your help !!!!!!!
Printable View
I am trying to use Insert Statement which is one of the field(format = Number) in Access = 0. My program does not understand data which is one of the field = 0. Can you help me to solve the problem.
I am appreciate for your help !!!!!!!
instead of making people download a file, virus scan it, un-zip it, guess at the file, guess wrong, guess at another file, open it, guess where in the code it is, get lost, get frustrated and give up.... how about just posting the code in question?
I'd also suggest putting [code][/code] tags around it so that it keeps the format...
-tg
I am trying to use Insert Statement which is one of the field(format = Number) in Access = 0.Code:rs.Open "SELECT Max([No]) as MaxField FROM SV where MSSV = '" & ws.Range("C" & i) & "'", con, adOpenDynamic, adLockBatchOptimistic
If rs.RecordCount > 0 Or rs("MaxField") <> Empty Then
con.Execute "UPDATE SV SET Status = 0 WHERE MSSV = '" & ws.Range("C" & i) & "' and Status = -1"
con.Execute "INSERT INTO SV([No],MSSV,MaLop,Ho,Ten,Diem,HanhKiem,[User],Status,[Date]) VALUES ('" & rs("MaxField") + 1 & "','" & ws.Range("C" & i).Value & "','" & ws.Range("A" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & "','" & tenUser & "','" & "-1" & "','" & NgayGioDangNhap & "')"
con1.Execute "INSERT INTO Lop(MaLop,TenLop) VALUES ('" & ws.Range("A" & i).Value & "', '" & ws.Range("B" & i).Value & "')"
Else
If rs.RecordCount > 0 Then
con.Execute "INSERT INTO SV([No],MSSV,MaLop,Ho,Ten,HanhKiem,[User],Status,[Date]) VALUES (1,'" & ws.Range("C" & i).Value & "','" & ws.Range("A" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & "','" & tenUser & "','" & "-1" & "','" & NgayGioDangNhap & "')"
con1.Execute "INSERT INTO Lop(MaLop,TenLop) VALUES ('" & ws.Range("A" & i).Value & "', '" & ws.Range("B" & i).Value & "')"
Example : Excel have field : Diem = 0 . Table SV (Diem(Value: Number)) = NULL
My program does not understand field Diem = 0 and it is not running.
Can you help me to solve the problem.
I am appreciate for your help !!!!!!!
Well, I'm running your really messed up code...you have a do: ??? loop out of order, missing two "end if"s and an if without a 'then'. Why don't you first, clean up your code, and just simply try to do ONE insert into your table without all the if statements. THEN, maybe I can help you find out why you can't insert. But until then, I'm outa here..that code is really, really confusing.
Dear SamOscarBrown,
I want to create the variable with the field Diem(value = Number) in Access as equal to ws.Range("F") because my program does not understand the line which is contain Diem = NULL.
My Excel have 7 fieldCode:con.Execute "INSERT INTO SV([No],MSSV,MaLop,Ho,Ten,Diem,HanhKiem,[User],Status,[Date]) VALUES ('" & rs("MaxField") + 1 & "','" & ws.Range("C" & i).Value & "','" & ws.Range("A" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & "','" & tenUser & "','" & "-1" & "','" & NgayGioDangNhap & "')"
MSSV MaLop TenLop Ho Ten Diem HanhKiem
1007 TH07 A Dung Nam Kha
As you know, Diem = NULL and the program could not Insert it because Diem (value = Number) is require Number and could not = 0. So I have to write the If Statement in order to the program is understand if Diem = NULL, the program insert normally.
I am appreciate for your help!!!!!
okay....then without relooking at the above posts, I'd simply do an if statement....
if NOT IsNull(Diem) then
con.Execute "INSERT INTO SV([No],MSSV,MaLop,Ho,Ten,Diem,HanhKiem,[User],Status,[Date]) VALUES ('" & rs("MaxField") + 1 & "','" & ws.Range("C" & i).Value & "','" & ws.Range("A" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & "','" & tenUser & "','" & "-1" & "','" & NgayGioDangNhap & "')"
End if
OR, as your last statement indicated...you want to put SOMETHING in the Diem field even if Diem is Null, then set Diem = zero (0) if it is Null
if IsNull(Diem) then
Diem = 0
end if
con.Execute "INSERT INTO SV([No],MSSV,MaLop,Ho,Ten,Diem,HanhKiem,[User],Status,[Date]) VALUES ('" & rs("MaxField") + 1 & "','" & ws.Range("C" & i).Value & "','" & ws.Range("A" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & "','" & tenUser & "','" & "-1" & "','" & NgayGioDangNhap & "')"
Dear SamOscarBrown,
I am trying to write If Statement but it is not running
Code:Dim diem as Double
If (ws.Range("F" & i).Value = NULL) Then
diem = 0
Else
diem = ws.Range("F" & i).Value
End If
con.Execute "INSERT INTO SV([No],MSSV,MaLop,Ho,Ten,Diem,HanhKiem,[User],Status,[Date]) VALUES ('" & rs("MaxField") + 1 & "','" & ws.Range("C" & i).Value & "','" & ws.Range("A" & i).Value & "','" & ws.Range("D" & i).Value & "','" & ws.Range("E" & i).Value & "','" & ws.Range("F" & i).Value & "','" & tenUser & "','" & "-1" & "','" & NgayGioDangNhap & "')"
Okay....but ya gotta tell us WHAT is not running?
Did you use the debug tool to follow the IF statement? Does it assign the approprate value to diem? If Diem is a number field in your database table, you will have to ensure your sql statement is written to insert the proper 'value' for Diem. It appears your sql above is ALWAYS attempting to insert a negative one (-1) for the Diem value....that is not what you want...you need to replace that value with your VARIABLE (which you need to declare (dimension) (integer, single or double)) Diem. (e.g. & "'" & Diem "'" & , instead of & "-1" &).
As I believe I have told you before (I mention this a lot to those use use 'con.execute'), it is always good practice when testing code to use a recordset object when inserting into/updating tables....then, it is easy to do a msgbox of the sql before executing it...gives you a bird's eye view of all the field names and values you are attempting to insert/update. By looking at the msgbox string, you should readily see if you have numerics which should be strings, dates which should have #'s, and so forth.
AND, when I insert Excel data into an Access table, I ALWAYS use a variable for each cell....some programmers probably do not, but it always helps me to see what I am about to insert. For beginners, I definitely recommend it...for those who are experienced and can quickly recognize numerics, dates and strings, then maybe not so important.
BUT, back to my first question...WHAT is not working, and what error(s) are you getting?
Dear SamOscarBrown,
Diem in the database have value = Number. I insert Excel data into an Access table and I get the error "Data type mismatch" because Diem = Null. I am trying to write If Statement but If Statement is not running.
Dear SamOscarBrown,
Diem in the database have value = Number. I insert Excel data into an Access table and I get the error "Data type mismatch" because Diem = Null. I am trying to write If Statement but If Statement is not running.
Rather than using = null use IsNull
Code:If (ws.Range("F" & i).Value = NULL) Then
diem = 0
Else
diem = ws.Range("F" & i).Value
End If
Alright, I see am getting nowhere fast....here is how to insert a number into a table: (Diem field is a numeric field, and myDiem is a number (in this case, an integer).
(assume cnn already dimensioned and connection established)
In your case, use an IF statement to set Diem = zero if Diem is NULL before you run the sql. THEN, use DEBUG to look at all values in your SQL string.Code:dim mySQL as string
dim MyDate as Date
dim myDiem as integer
myDate = Date
myDiem = 4
mySQL = "insert into test (MSSV, Ho, Malop, Ten, theUser, status, dateUpdated, Diem) values ('SAMMI', 'TEST', 'John', 'JONES', 'SAM','1',"
mySQL = mySQL & "#" & myDate & "#," 'I broke this apart so you'd see how to enter a DATE
mySQL = mySQL & Str(myDiem) & ")" 'and this I separated so you can see how to enter a NUMBER
cnn.Execute mySQL