[RESOLVED] Form View & MySQL

[SuperRookies]

Hello again,
Can someone tell me how to edit/update/delete record on the Form View?

i got this on my Update COMMAND :

UPDATE agent SET Code = ?, Name = ?, Addr1 = ?, Addr2 = ?, Addr3 = ?, Addr4 = ?, PhoneNo = ?, HPhoneNo = ?, FaxNo = ?, OperateNo = ?, PhoneModel = ?, GroupCode = ?, CreateBy = ?, Remark1 = ?, CreatedDateTime = ? WHERE ID = ? AND Code = ? AND ((Name = ?) OR (Name IS NULL AND ? IS NULL)) AND ((Addr1 = ?) OR (Addr1 IS NULL AND ? IS NULL)) AND ((Addr2 = ?) OR (Addr2 IS NULL AND ? IS NULL)) AND ((Addr3 = ?) OR (Addr3 IS NULL AND ? IS NULL)) AND ((Addr4 = ?) OR (Addr4 IS NULL AND ? IS NULL)) AND ((PhoneNo = ?) OR (PhoneNo IS NULL AND ? IS NULL)) AND ((HPhoneNo = ?) OR (HPhoneNo IS NULL AND ? IS NULL)) AND ((FaxNo = ?) OR (FaxNo IS NULL AND ? IS NULL)) AND ((OperateNo = ?) OR (OperateNo IS NULL AND ? IS NULL)) AND ((PhoneModel = ?) OR (PhoneModel IS NULL AND ? IS NULL)) AND ((GroupCode = ?) OR (GroupCode IS NULL AND ? IS NULL)) AND ((CreateBy = ?) OR (CreateBy IS NULL AND ? IS NULL)) AND ((Remark1 = ?) OR (Remark1 IS NULL AND ? IS NULL)) AND ((CreatedDateTime = ?) OR (CreatedDateTime IS NULL AND ? IS NULL))

but when i click the update command , it return this error message ...pls help.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, Name = ?, Addr1 = ?, Addr2 = ?, Addr3 = ?, Addr4 = ?, PhoneNo = ?, HPhoneNo =' at line 1

[gep13]

Hey,

Where did you get that update statement from? Is all that logic actually necessary?

Have you tried executing the command directly against the database? Do you know that it works? The error message would suggest that it is the query that is wrong. Can you show the markup and server side code that you are using?

Also, I would suggest that you use named parameters. This would make that query far easier to deal with.

Gary

[SuperRookies]

i don understand how does the command and parameter editor works.
how to pass the parameter ?

my value is 1 of the textbox inside the form view.
how does the parameter source work? i cant found my control ID on the list.

[SuperRookies]

ok~ i already named all the parameter as below :

UPDATE agent SET Code = @Param1, Name = @Param2, Addr1 = @Param3, Addr2 = @Param4, Addr3 = @Param5, Addr4 = @Param6, PhoneNo = @Param7, HPhoneNo = @Param8, FaxNo = @Param9, OperateNo = @Param10, PhoneModel = @Param11, GroupCode = @Param12, CreateBy = @Param13, Remark1 = @Param14, CreatedDateTime = @Param15 WHERE (ID = @Param16) AND (Code = @Param17) AND (Name = @Param18 OR Name IAS NULL AND @Param19 IS NULL) AND (Addr1 = @Param20 OR Addr1 IS NULL AND @Param21 IS NULL) AND (Addr2 = @Param22 OR Addr2 IS NULL AND @Param23 IS NULL) AND (Addr3 = @Param24 OR Addr3 IS NULL AND @Param25 IS NULL) AND (Addr4 = @Param26 OR Addr4 IS NULL AND @Param27 IS NULL) AND (PhoneNo = @Param28 OR PhoneNo IS NULL AND @Param29 IS NULL) AND (HPhoneNo = @Param30 OR HPhoneNo IS NULL AND @Param31 IS NULL) AND (FaxNo = @Param32 OR FaxNo IS NULL AND @Param33 IS NULL) AND (OperateNo = @Param34 OR OperateNo IS NULL AND @Param35 IS NULL) AND (PhoneModel = @Param36 OR PhoneModel IS NULL AND @Param37 IS NULL) AND (GroupCode = @Param38 OR GroupCode IS NULL AND @Param39 IS NULL) AND (CreateBy = @Param40 OR CreateBy IS NULL AND @Param41 IS NULL) AND (Remark1 = @Param42 OR Remark1 IS NULL AND @Param43 IS NULL) AND (CreatedDateTime = @Param44 OR CreatedDateTime IS NULL AND @Param45 IS NULL)

let's say i have a txtCode inside the Form View.but how to get the value?
I don see the controlID on the list.

pls help...

[gep13]

Hey,

Which list are you referring to? I tend to do all my parameter handling explicitly in code, rather than using the "wizards" that you can use within Visual Studio. You can still do it the way I think you are trying to do it, but I would encourage you to take more control over your code, you will find it far easier in the future.

Gary

[SuperRookies]

ya ~ i understand.
i know how to code but i tot the wizard can make me easy to maintain in the future, plus i'm lazy to code .

[gep13]

Hey,

That really depends on what to deem as "easy". I find it easier to handle all of this in code, as you get more flexibility, and there is a logical separation of code and display logic.

Can you show which wizards you are referring to exactly?

Gary

[SuperRookies]

hi Gep,
can i show me some example about 'how to update data with FormView'.
i don understand how the FormView Template work, and i don know how to retrieve the value from the textbox inside the formview template....

[gep13]

Hey,

Most tutorials will suggest that you use the following:

http://www.beansoftware.com/ASP.NET-...w-Control.aspx

But I would suggest that you take a little bit more ownership of the FormView.

I would use something like the following approach:

http://msdn.microsoft.com/en-us/libr...mupdating.aspx

Hope that helps!!

Gary

[SuperRookies]

ok thanks a lot~ now i understand how it works.
Do you know how to set the parameter with vb instead of using HTML?

<asp:Parameter Name="ID" />

[gep13]

Hey,

Yip, just create an instance of a Parameter, in your case, a MySqlParameter.

Here is the documentation for a SqlParameter, but the technique is exactly the same for MySql:

http://msdn.microsoft.com/en-us/libr...withvalue.aspx

Gary

[SuperRookies]

OIC....

Thank you.

[gep13]

Hey,

Not a problem at all. Let me know if you have any issues, and I can dig up another sample for you.

Gary

[SuperRookies]

ok thanks...i need times to digestion.
i think i will need ur advise soon

[mendhak]

Using wizards does not make future maintenance easier, especially if you need to make big changes to your application.